Comments on: Advanced VLOOKUP in Excel: multiple, double, nested

In this VLOOKUP tutorial, you will find a number of advanced formula examples that demonstrate how to look up with multiple criteria, use two VLOOKUP functions in one formula, dynamically pull data from different sheets, and more. Continue reading →

Comments page 6. Total comments: 540

  1. Hello,
    I want to run a daily manning report from an excel roster but don't know how to go about it. I want it to be set up as classifications and Manning Numbers and then have total of each classifications and so it has to provide having personnel showing as on isolations from the current manning as well.
    Can you help me?

  2. i have C collumn Col-A,Col-B & Col-C
    Under Col-A 4 Data(A1=Name,A2=Ram,A3=Ram,A4=Ram)
    Under Col-A 4 Data(B1=Salary,B2=Blank,B3=Blank,B4=4000)
    Under Col-A 4 Data(C1=Attendance,C2=Blank,C3=P,C4=A)
    Suggest me formula by Vlookup & Index where i can get the data from Col B(Salary) & Col C(Attendance).after Blank.Name is same in A col when run the formula based on Name then form the array provide me 4000 from B col and P from C col. its meance it check data if found blank then move to second and at end provide me after blnk i case of same name.
    alwasy provide me data after scape blank cell and swap next row.

    Name Salary Attendace
    Ram
    Ram 4000
    Heera A
    Ram 3000 P
    Heera 2000 A
    Heera 1000 A
    Ram 500 A

    Name Salary
    Ram 4000 Result always this

    Name Attendace
    Ram P Result always this

    Name Salary
    Heera 2000 Result always this

    Name Attendace
    Heera A Result always this

    1. Still i dont recive any solutions

  3. Thank you for the detailed instruction for different uses of VLOOKUPs.
    I'd like to find out if I could use VLOOKUP to look for a value when a condition applies in the lookup table. For example, =VLOOKUP(Lookup_value_list, lookup_table!A1:D100, 2, false) Under column B, let's say it has certain number of Value As and certain number of Value Bs, I want to add a condition (like a filter to the lookup_table) to ask the vlookup funtion to only look for Bs in the lookup_table. How do I do this without applying for a filter before hand? Thank you in advance!I would appreciate your insights!

  4. Is it possible to import every Nth Cell from another Sheet. I am trying to import every 6th cell from Column C. I’ve tried doing this but I keep ending up with the imported data appearing every 6 rows. I then tried to sort the range this compiled the data but also alphabetised it which I don’t want. Thanks!

  5. Hello,

    I wanted to know on how can I pull data for a certain information like "XYZ" from the data provided below.

    Item Set Code Test
    ABC, IJK, RST, XYZ 5 2001 Major
    ABC 2 251 Major
    IJK 6 4001 Remission
    RST 6 9002 Depression
    XYZ 9 12003 Remission
    IJK, XYZ 10 8009 Remission
    ABC, RST 11 4007 Depression

    Thanks

  6. In the single cell we have multiple values like this CAA
    CBG
    ERT
    HGJ
    when i am trying vlookup 1st value (CAA) only coming , remaining values not come.

    1. Hello Thavakumar!
      For me to be able to help you, please describe your problem in more detail. What values are there in the cells you are applying your formula to?
      Are your 4 values typed in the same cell or in 4 different ones?
      What formula are you using to look for values?
      Please let me know. I think I can suggest a solution but some additional information is needed.

  7. Hi i want use vlookup formula but i have issue to find out result with two lookup value result will be same
    as a expamle :
    THIS IS DATA
    A B C
    ORENGE FROUIT
    APPLE FROUIT
    POTATO VEG
    TOMATO VEG

    a b
    1ST LOOKUP VALUE 2ND LOOKUP VALUE FIND OUT RESULT
    ORENGE APPLE ????????

  8. Good day, I am busy with big recons. I want to match info lines (A-X) with info lines (Y-AV) but my key codes is in G and AD). How do I do 'n v-lookup dat say "match code in G with Code in AD, if it matches in the same line 227 it must say yes and if it doesn't match in the same line it must say false. I hope I make sense.

  9. thank you so much.

  10. Hi
    I need to vlookup (one column have part numbers) and
    (another have part numbers in between - as separating 3 integers).
    Eg. 12345678
    123-456-78
    but both are same.
    Kindly assist me how to make it
    Thanx

    1. just add CONCATENATE before your vlookup

  11. thank you so much.
    you are my hero.

  12. i want get multipal value in sheet like, Amir Have got Amount 5 time and 5 payment are in different Row vloook will got 1st amount and sumif will count All amount but i want like this
    1st Amount 151
    2nd Amount 515
    3rd 120

  13. Hey guys can teach me. If I have entire of Emp Name with staff Id and Date. How am I going to run the VBA excel in a diffent worksheet.

    Thank you
    All angel

    1. You can simply use this code
      Sheets("Sheetname").range("Cell name").value
      further i can teach you on team viewer.
      +923220000671 is my whatsapp numebr.

  14. Hi ,
    Kindly Help With vlookup function of Below Criteria.
    column A b
    row 1 Type value
    Row 2 Apple,mango = vlookup( ?? ) result should be 5

    Data
    column A b
    row 1 Type value
    Row 2 Apple 2
    Row 3 mango 3

  15. Hi! I have a large database (over 12,000 rows and A to AZ columns) of a name dictionary. It is in constant flux, as some details change or new names come up. I need to prepare for publication a worksheet of all those names that have a certain info in one of the columns. I could do that by filtering, but I would need to stop updating the main database. Can I somehow create a linked worksheet with the names that CURRENTLY have that info but that gets automatically updated if changes (more names or change of info) occur in the main database? I could create a dynamic table and do a Vlookup, but I would get all the empty rows as well, which I don't need.
    Thanks for your help.

  16. Hello

    I need a vlookup formula which searches for the most correct result.

    The situation is as follows. I have a table of hundreds of cities, there cities have specific numbers given. I want ecxell to give me the number of Vienna Oldtown even if I just type Vienn or oldtown.

    Thx for your answer

  17. work book1
    date card number reg amount
    01/01/2019 1076206 60719 80.25
    02/01/2019 1076206 60719 13.00

    work book2
    date card number reg amount
    01/01/2019 1076206 60719 80.25
    02/01/2019 1076206 60719 13.00
    HOW TO RETURN THE CORRECT VALUE TO " WORK BOOK 1", if we used vlookup its return the first lookup value both card number. but it is wrong.

    pls help

  18. Hi,
    I'm sharing few data as given below..request you to please share the solution for this.
    Raw Data Lookup_Value OutPut
    NYM1035/323141085 323141085 NYM1035/323141085
    1218454/UEI00007463 1218454 1218454/UEI00007463
    1219385/UEI00007485
    399152/919095
    1200053831628 / 1200054080311
    399381/919186
    918447 / RAHUL.U713051981455
    TXPC5141 / 323177477
    YASWANTHUPUDI123406121991198/918491
    sgumparthi3410061970989/918559
    OLESH0000000659482052639/1GPZ070050
    QFAM5567 / 323338673
    323370031 / RIWR2689
    323425520 / BVFG6740
    YMUE0863 // 323284372
    XKTM6146 // 323295089
    5021777833/919207
    NFBW0723 / 323276824
    399514 // dsankar00615081968136
    919240 / kashishkumar201902071989968
    397760 / ME.PRITAMGHOSH10061992935

  19. Multiple latest 6 dates from name A1 dates B2 on sheet 1 vertically (Name appears over 20 times with multiple names same quality of times & dates appears over 100 times with multiple dates same quality of times ) to return on another sheet 2 with all information horizontal in formula of latest date / second latest date / third latest date / & so on.
    Name is the start A1 to match latest date B2 CRITICAL for rest of information B to AW
    Have Changed work sheet 2 with name to start from A1
    Oldest Dates B2 start from top of sheet with latest date at the bottom & keep adding to list to bottom
    Have used ; =Vlookup(A4,Section!A:B,2,false) — found old date. NOT LATEST
    {=Max(if(section!A4:A20000=A4,Section!B4:B20000))}
    =Min. As above.
    =Arrayformula(Vlookup (A4,section!A4:B20000,2,false))

    If I have space between one date to the next date does this matter as I have separated all following dates?

    Sheet size is 30 columns A - AW & rows 1 - 50,000 plus will grow in size with added information down the sheet.

    I hope you can help with this formula to match dates & 6 latest in order & the information to the right of these dates.?
    Regards
    Tony

  20. dear, I need help. For Example, IF we have many entries against value "A" than how I can pick the latest date value using the formula in excel. and if I have many entries of different values how I can pick the latest date value. (Sorry For My English)

  21. hey; i need your help; i have a sheet containing 500 types of equipment each equipment has 2 or three prices and i need to get from it the min price , average and max prices and returning the supplier name for each value

  22. Hi, I'm trying to create a v-lookup formula that will look at the corresponding ID in column 1 of sheet1 (which contains my formula), then lookup the ID on sheet2 and bring in the data from column 7 of sheet2 IF... column 6 = 2019. I do v-lookups between sheets all the time and have been trying to put it together with an IF formula but I haven't been able to get it to work. Can you help?

  23. Hi madam,
    i refer above all step for my case, but still not found a solution. i have 2 kind of excel sheet. when i select one date in 1st sheet need to get 2 detail from another sheet according to selection data. i try to lot of time & through the every step. but couldn'd. please help me....

  24. Hi,
    i am having some product in my master sheet. and i am having month wise sheet like sheet 1= jan,sheet2= fab,...now i enter randomly product name in month wise sheet and his corresponding value. for using vlookup formula i collect corresponding product value from sheet1= jan by(=VLOOKUP(A9,'jan'!B7:F26,5,0). so now i want to collect value for same product in other remaning month and adding value form all month in master sheet. please help

  25. Wonderful tutorials! How helpful!
    I've looked for a vlookup formula to add figures with common letter code down columns. Example (Letters col. A, $Values col. B):

    AAA $330
    AAA $600
    AAA $270
    BBB $300
    CCC $100
    CCC $120

    On my separate spreadsheet, I would like to add all AAA values in one cell, $1,200; all BBB $300; CCC $220, etc.

    Thanks!

  26. Hi,

    How can i get different variable with same answer
    For example

    Name Roll nol
    amit 1
    vikas 2
    ajeet 3
    raj 4
    manish 5
    sanjeet 6
    suraj 7
    amit 8

  27. My data is given below:

    A B C D
    Product Rohim korim jodu
    TV 100 200 300
    AC 301 302 303
    FREEZER 3002 3005 3009

    After enter the following data I need result (?)
    A B C
    Rohim TV ??? (result should be 100)
    Korim AC ??? (result should be 302)

    How can I do it with vlookup or other formula in column C . Pls help.

  28. hi
    i am learning excel and i am using a football table,what i`m stuck on is when i enter the weeks results i now want to be able to look for a teams name and be able to bring up its last 5 results to show its current form,i can do this with a pivot table but its time consuming doing every team i was wondering if it can be done to show the whole leagues current form

    thanks
    colin

  29. Hi,
    In the below formula what is the ! symbol for?
    =LOOKUP(D26,'B:\HPServices\Current\[Material Price Book.xlsx]Material Pricing'!$B2:$B196,'B:\HPServices\Current\[Material Price Book.xlsx]Material Pricing'!$H2:$H196)

    Brian

  30. =IF(ISNA(MATCH(A2, Individual!$A$2:$A$108385, 0)), VLOOKUP(A2, Individual!$A$2:$A$108385, C2&"/"&C3, FALSE)," ")

    Row ID Relationship Name
    132361 Father Buchi Ramulu
    132361 Mother Sujatha
    132364 Father Mahesh
    132387 Father B.Ramulu
    132387 Mother Kondamma
    132390 Father Anjaneyulu
    132390 Mother Laxmamma

    This Row ID should match with other sheet Row ID and return father & mother name "Father/Mother" in single cell.

    1. =CONCATENATE(B2&C2) 132361 Father Buchi Ramulu 132361 Father =VLOOKUP(CONCATENATE(F2&G2),A:D,4,0) Mother =VLOOKUP(CONCATENATE(F2&I2),A:D,4,0) =CONCATENATE(H2,"/",J2)
      1. in other sheet, do text to column. Put Unique ID and Father and Mother in col.
      2. First look up( concatenate father and id and mother and id) in Raw Data and look up through Lookup.
      3. then concatenate mother and father.

  31. HiHi,
    I have query regarding find/search and vlookup.
    I want find specific text from cell (string) and retrieve data from vlookup table by using this specific text.
    e.g. “This is a boy” or “This is a girl” is in one cell.(there will more than 100 sentences) In Vlookup table Boy = Male and girl = Female.
    I want display Male or Female in another cell of same sheet depends on cell string contains boy or girl.
    Regards,
    Pradeep

  32. need a formula in vlookup for 2 scenario

  33. Can Someone please advise me, how can i have value of one label appearing multiple times in Col-A and its return value in Col-B

    For Ex

    Col-A Col-B
    ABC 1
    ABC 2
    ABC 3
    XYZ 4
    CBA 5
    ABC 6
    ABC 7
    FFF 8

    to other sheet like below

    ABC 1
    ABC 2
    ABC 3
    ABC 6
    ABC 7

    Thanks
    Sudip

  34. Vlookup to get 2nd occurrence using Indirect function is not working. It's giving the first occurrence only. It's working in the sample file attached. But it's not working in real time scenarios.

  35. =VLOOKUP(B$1,RT,2,)
    Kindly Explain

  36. how to concatenate a coloumn data from A1:A10 without using formula =CONCATENATE(A1&A2&A3&A4&A5&A6&A7&A8&A9&A10)

    IS THERE ANY OTHER FORMULA FOR GOT CONCATENATE OF A1TOA10 CELLS VALUE AT ONCE

    1. you can use cocat function in excel2019

  37. Hi Ablebits!
    Thanks Very much! Your tutorials do me great job!
    My vlookup dosent work out, it returns N/A i have tried all trouble shootslike advised.
    Question; Can the version of excel be an issue, besides does the fomat of the cell be a matter.
    waiting
    Ronald

    1. Hi Ronald,

      Vlookup works in all versions of Excel, but the format of the cell can be an issue, for example a number formatted as text. You can find a list of the most common reasons for #N/A and other errors in this tutorial:
      Excel VLOOKUP not working

  38. How do we check using vlookup which customer ordered Apple

    Column A = Customer
    Row 1A= John
    Row 2A= Susan
    Row 3A= Sherry

    Column B = Product
    Row 1B= Grapes,Apple
    Row 2B= Apples,Lime
    Row 3B= Pear,Pineapple

  39. HI

    I have an excel data like following, i want to retrieve style DEC and PQR becasue all their SKU's had sale. do not want to retrieve data for styles whose all SKU's did not have sale.

    How to go about it?

    Style SKU Sale
    ABC 111
    ABC 222 Yes
    ABC 333 Yes
    ABC 444 Yes
    ABC 555 Yes
    DEF 1111 Yes
    DEF 2222 Yes
    DEF 3333 Yes
    DEF 4444 Yes
    DEF 5555 Yes
    PQR 121 Yes
    PQR 212 Yes
    PQR 312 Yes
    PQR 412 Yes
    PQR 512 Yes
    PQR 612 Yes
    PQR 712 Yes
    LMN 322 Yes
    LMN 422 Yes
    LMN 522 Yes
    LMN 622
    LMN 722

  40. Hi, i am using the following VLOOKUP comment which works great! and really could do with this working in Hlookup however it doesnt work, are there different parameters for Hlookup?

    Vlookup
    Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
    ‘Updateby Extendoffice
    Application.Volatile
    Dim xRet As Variant ‘could be an error
    Dim xCell As Range
    xRet = Application.Match(LookVal, FTable.Columns(1), FType)
    If IsError(xRet) Then
    VlookupComment = “Not Found”
    Else
    Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
    VlookupComment = xCell.Value
    With Application.Caller
    If Not .Comment Is Nothing Then
    .Comment.Delete
    End If
    If Not xCell.Comment Is Nothing Then
    .AddComment xCell.Comment.Text
    End If
    End With
    End If
    End Function

    Hlookup
    Function HlookupComment(LookVal As Variant, FTable As Range, FRow As Long, FType As Long) As Variant
    ‘Updateby Extendoffice
    Application.Volatile
    Dim xRet As Variant ‘could be an error
    Dim xCell As Range
    xRet = Application.Match(LookVal, FTable.Rows(1), FType)
    If IsError(xRet) Then
    HlookupComment = “Not Found”
    Else
    Set xCell = FTable.Rows(FRow).Cells(1)(xRet)
    HlookupComment = xCell.Value
    With Application.Caller
    If Not .Comment Is Nothing Then
    .Comment.Delete
    End If
    If Not xCell.Comment Is Nothing Then
    .AddComment xCell.Comment.Text
    End If
    End With
    End If
    End Function

    Any help would be greatly appreciated ?

    1. i have a some doubt on this

  41. I am having SKu data in one sheet and i have pan india sku data in othr sheet , actully i want find out that first sheet sku qty. Location wise in first sheet against each sku.pls help

  42. The first column is the item number

  43. MAS Code 23% 18% 15% 12.50% 10% 7.50% 5% 5%
    10100 0.29 0.21 0.19 0.18 0.17 0.16 0.14 0.10
    10101 0.29 0.21 0.19 0.18 0.17 0.16 0.14 0.10
    10102 0.29 0.21 0.19 0.18 0.17 0.16 0.14 0.10
    10103 0.29 0.21 0.19 0.18 0.17 0.16 0.14 0.10

    i need to pull the commission based on the price that is selected from dropdown list. I dont know how to do that.

  44. One can use the VLOOKUP function with 2 criterias WITHOUT any Helper-Column.
    It will become an Array-Formula with some concatenations BUT within the formula.
    *** My solution is presented in the below linked picture: https://postimg.org/image/mdmfjj7gd/
    ----------------------------
    Michael (Micky) Avidan
    “Microsoft® Answers" - Wiki author & Forums Moderator “Microsoft®” Excel MVP – Excel (2009-2018) ISRAEL

  45. Hi i am very excited about using formulas i excel after reading this page. please help me to solve the problem that i have... i have set of numbers related to my field.. for instance.,
    spool no j.no fit up no
    16011828-2 6
    16014266-1 1OSW
    16010115-1 2
    16011586-1 2
    14010644-1 7
    21060012-1 17 24865 / 24842
    14060009-1 4
    12030178-1 4
    14010630-1 4FFW
    16011442-1 1
    16011925-1 3FFW
    16011815-1 2
    14010914-2 6
    16011440-1 6
    12010688-1 2
    14010581-3 13
    14010088-1 1
    14010619-1 1FFW
    16090078-1 6
    17010191-3 9
    12011083-3 7 04574 / 4555
    16014313-1 3FFW 21761 / 21766
    17010135-2 7
    14010658-1 1
    16011816-1 4
    14010643-2 8FFW
    14011316-2 3
    12010579-2 7
    14010089-1 4
    16030591-4 10
    16011263-2 13FFW
    17010945-2 12
    16010822-2 7
    12010632-2 7
    16090630-3 8FFW
    14010659-2 5
    16011165-1 4 24199
    14010500-2 12
    16011829-1 2
    12010644-2 13
    12010631-1 5
    17010205-2 8FFW
    16011167-1 3
    14010581-1 1FFW
    14010660-3 8FFW
    16013899-1 2
    12010689-2 5
    16011842-1 1FFW
    16011730-1 6
    17010208-3 7
    12010551-2 13
    17010176-1 5FFW
    17010140-1 2
    14010676-1 5FFW
    16011898-2 12
    14010623-2 8
    12010688-1 1
    17010147-2 11
    16013031-2 17
    12010641-3 10
    12060018-1 2
    14010677-2 8
    12010553-2 10
    12010057-2 14
    14011050-2 10
    16011852-1 8
    17010301-1 1FW
    14030082-1 3FFW
    16011167-3 11
    16011166-2 6
    14010498-3 10
    like this,, i want to get all the occurrences of my selected spool no,
    =IFERROR(INDEX($C$2:$C$63399,SMALL(IF($F$2=B2:B63399,ROW(C2:C63399)-1,""),ROW()-3)),"") I can use the formula,, but i cannot use it continuously,, after finishing one set of calculation..this formula for only one set of calculation,,, and i want to use the array in row vise(spool no only). not column wise,, please any one help me to fix the problem.. thank u..

  46. As you have explained this formula returns the 2nd instance:

    =IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")

    Where do I edit the formula to return the the 3rd 4th 5th etc

    Thanks in advance...

  47. I have an tool inventory check out sheet that references a table where there is equipment type column, a description column, followed by an ID number column. I am looking for a formula that after the equipment type is determined, the descriptions are limited to that equipment type,and then the equipment ID number.

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  48. Dear Sir/madam

    I tried if condition with vlookup.I got the required result for few cases.Though the required data is there in the next sheet i couldn't get that.How can i get that for remaining cases.

  49. Thank you for this article! It was immensely and has worked quite well.

    The only issue I am having is that it is retaining the spaces between cells with the output...for example, in your sample above for all occurrences of lookup value (duplicates), Dan Brown’s Biscuits and Dan Brown’s Cherries have 6 rows between them...for my output, these rows remain but are blank/#NA...any good ideas as to why this may be?!

    Thanks any and all for your insights!

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  50. Hi, i have a table with 50rows (drugs) and 30 columns (Citties) and sales qty of drugs are spread for each Citty. I need that this table of data returns in 3 columns named : City , Drugs,Sales Qty.
    How can i do this ?
    Br.Odi

    1. Hello,

      Please try to solve your task with the help of the Unpivot Table tool which is a part of our Ultimate Suite for Excel. You can download its fully functional 14-day trial version using this direct link.
      After you install the product, you will find Unpivot Table in the Transform section under the Ablebits Tools tab.

      Hope this will help you with your task.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)