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 10. Total comments: 540

  1. HI,

    I am trying to easily portray an inventory availability list that instead of using style and size codes, simply indicates if a a style has the sizes in stock.

    For example. Item: MD195, has sizes XS, S, M, L, XL. however, each size has a specific #, XS = MD195-01-31, S = MD195-01-32, etc.

    I am trying to get it to update every time i load the current inventory, if it is available in XS, put yes or no, Small yes or no, etc.

    The real difficulty comes when I try to drag the formula to apply to all cells, while referencing the appropriate cell(s).

  2. I applied vlookup multiple rows with the same lookup value with index farmulla, my same lookup values are more than 3 i.e. if it if 10, then it shows only 1st value as a result and then #num error. rest of 9 values are not shows. i copied the farmulla 10 times... but results not came.

    pls help me with this erros

  3. Excel VBA VLOOKUP successfully returns matching data AND also returns an Err.Number = 1004. The datum are dates. I am suspecting the data types as being the issue. Since the VLOOKUP is successful it does not present a problem but, it is puzzling and I would like to be certain there is not underlying issues.

    Why would a successful VLOOKUP throw a false error?

  4. Hi, i need ur assistance to get the result using formulas especially using vlookup. Example i have a large data. In a column i have a account numbers with different branches. Also few account numbers are same but different branches. Is it possible to get the result using vlookup with same account number with different branches from the large data. Please help me...

    And i would like to say thank you for providing the detailed functions of formulas. It is really helpful for me.

    1. Use
      =vlookup(a2&b2,data,3,false)

  5. Sara, please read the section above entitled "How to do two-way lookup in Excel". I think this is what you need to implement.

  6. Hello,
    Try to write a formula with if and vlookup and I think I got it to work but I'm having trouble making to work for a entire year I need.

    Example:
    Sheet 1
    October November
    Names Hours Hours
    John Smith 25 15
    Katie Wilson 45 55
    Jeremy Pate 37 20

    Sheet 2
    October
    Hours
    Names
    John Smith
    Katie Wilson
    Jeremy Pate

    =IF($B$1='Sheet1'!$B$1,VLOOKUP(A1,'Sheet1'!A:K,2,0),"no")

    With this formula if B1(October) in sheet1 than it matches and the formula shows me the hours. However for November unless I change the formula I can't get the hours to appear in Sheet 2. Sheet 2 has ONLY one column for hours and I can't add anymore due to reporting restrictions. How do I get excel to automatically update it self if change "B1" Cell from October to November.

    Please help. Thank you

  7. plz clarify following senario,

    Table _ 01
    Compay No of Employees
    aaa 221
    aab 245
    aac 250
    aad 290
    aae 271
    aaf 340
    aag 193
    aah 213
    aai 90

    Table _ 02
    Compay No of Employees
    aaa 196
    aai 345
    aab 650
    aag 193
    aae 278

    Expected result

    Table _ 03
    Compay No of Employees
    aaa 196
    aab 650
    aac 650
    aad 290
    aae 278
    aaf 340
    aag 193
    aah 213
    aai 345

    I used formula =VLOOKUP(D4,A$17:B$21,2,0) and ends up with following results

    Table _ 04
    Compay No of Employees
    aaa 196
    aab 650
    aac #N/A
    aad #N/A
    aae 278
    aaf #N/A
    aag 193
    aah #N/A
    aai 345

    how can i obtain correct results

    NB:

    i need to update table 01 referring table 02 and if data entry is not available in table 02 the data entry in table 01 should be remain as it is.

    appreciate your kind response thanks and regard

  8. In your section “How to get all duplicate values in the lookup range” you explain how to use Index, Small and Row to generate the list of values. I think there is a simpler way. Try the following. Select the range F4:F10 or a range with enough rows to hold the maximum number of expected values. Enter the following in the formula bar and then press SHIFT-CTRL-ENTER
    =IF(B2:B16=F2,C2:C16,"")

    I hope this works. I would like some help too however. I would like to make the list created by the above array formula to appear in the dropdown box for the allowed values for another cell i.e. using the Excel Data Validation functionality. However, I cannot get this to work. It seems as if array functions cannot be entered into the Data Validation entry box. Do you have any suggestions?

    Ian

  9. I'm having difficulty getting one of your examples to fit my scenario. I have 2 tables. Each includes the person's name and a date. I want to match the name / date combination in one table with the same combination in the other table, then return a third field.

    In one table, I have dates across the top (column headings) and names down the side (row headings). I want to match the date / name combination from that table to the date / name combination in another table (see below) and return another field, in this case the city.

    10/9/16 Bob Boston
    10/16/16 Phil Philadelphia
    11/13/16 Pete New York

    Any help would be appreciated.

    Thank You.

  10. Thank you so much for this useful tips. Really reducing my work by multiple folds.

    Regards,
    Aravind

  11. HI I have a 3 page workbook I have built and on all is number to be referenced in column A sheets 1 - 3. I have been sent another workbook that has the same numbers in column A. I need to in column B on the one sent in to reference the number in column A and locate that single occurrence of that number on My 3 pager and input the corresponding data from each column N on my 3 pager. I assume I will need 3 Vlookups but how do I lay that out in the Cell B on the one sent ?

  12. Hi
    I'm trying to get all duplicate values in the lookup range from pivot table report into manual customised report.
    1. Pivot table worksheet have multiple duplicate names in the first column with various values in the eighth column.
    2. Summary worksheet have manual customised report layout with merged rows of all the names which are in the pivot table report.
    3.I would like to display all the values of those duplicate names from the pivot table to the manual summary worksheet report.
    Is that possible, please give me the right formula.
    I tried {=IFERROR(INDEX($K$7:$K$60,SMALL(IF($P$5=D7:D60,ROW(K7:K60),""),ROW()-3)),"")}, but it shows no values.
    please help.
    thanks in advance

    1. Try This ROWS($E15:E15)),2) instead of this ROW()-3)),"")
      ($E15:E15)) is a resulting value, where you want your result,,,
      If your answer value is in 2nd coloumn then type 2, if it is in 4th coloum then type 4 and close bracket

  13. How to use VLOOKUP to get maximum value
    i have 2 worksheets
    sheet1( call it a data sheet)
    columnA columnB
    name marks

    robert 2
    robert 3
    robert 4
    charles 2
    charles 5
    charles 6

    i want to write a vlookup or a suitable formula in sheet2 which will pull max marks from sheet1 and get the results

    for e.g.if i type robert it will dispaly 4 as the answer( maximum value )
    sheet2 ( formula sheet)
    columnA columnB
    name max_marks
    robert 4
    charles 6

    any help!

  14. How to apply double vlookup in single cell.is there any possibility.

  15. Good Lesson

  16. Hi,

    I have a 2 column table (Wards and teams) to know what team does roadworks in what ward.
    Some roadworks are in multiple wards however, needing several teams.
    In this event, the column 'wards' in another table (called roadworks) would show each ward separated with a '/'. Some ward names have spaces in them.

    How would I do a Vlookup that shows me all the teams that should be contacted for a roadworks' job?

  17. No response needed ... this is just a complement.

    "This web site is remarkable!!! I had always wondered how to go about solving some of the more complex problems I face at work; I can now say that I can, thanks to this web site. Thank you so much !!!! Alex."

  18. hi,
    if i want the formula return in column instead of row

    {=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}

  19. Instead of images, if you can use actual tables for your examples, we can copy and experiment in our Excel environment. Thank you!

  20. I've been working on a 2 criteria VLookup and have it working, but I need to know if there's a way to display the found data in the same format as it exists in the source table.
    The columns I'm pulling data from may contain "$ values", or "% values".
    (Ex: Where 'Name=Neal' and 'Month=Mar')

    Name Jan Feb Mar Apr May Jun
    Pete $26 $34 $33 $37 $41 $44
    Sally $41 $29 $0 $49 $24 $34
    Neal $42 $36 100% $38 $25 $37
    Diana $40 $18 0% $38 $35 $32

    I've tried using conditional formatting on the 'results' fields, to change the format based on value, but I can't figure out when "'$0' vs '0%'", 100% vs $100, etc.

    I thought I could expand my vlookup to include the Cell() function to return the formatting, but don't know if this is the nest way.
    Any thoughts?

  21. if i have data like

    code code no.
    1001 2001 1
    1002 3001 2
    1003 1001 3

    i want to lookup 1001 in different work book & no. infron of code

    kindly help which formula to use

  22. very useful info you have there. thank you very much. but i could not make the formula work. can you please help me? i use vlookup for a specific text(column A) and data next cell (column B, col_index_num)) but when the text is in more than one cell, i need the different datas concatenated with space. Can you please type the formula?

  23. Hi There
    I you can help. I am trying to get the below formula to work. It is bringing me back the Case values but N/A for anything else.
    Essentially I have a code in a cell and I want to know which column this code corresponds from three columns of codes on another sheet.
    This is the formula I have tried. I have tries nesting IF statements combined with vlookup statements. If at all possible I would like to have this all in one cell rather than spread out. Any help would be much appreciated.
    =IF(A2=VLOOKUP(A2,Compare!H:H,1,FALSE),"Case",IF(A2=VLOOKUP(Sheet1!A2,Compare!G:G,1,FALSE),"Shrink","Item"))

    I have also tried

    =IF(VLOOKUP(A2,Compare!H:H,1,FALSE)=A2,"Case",IF(VLOOKUP(Sheet1!A2,Compare!G:G,1,FALSE)=A2,"Shrink","Item"))

  24. Dear sir,
    If you please help me to solve the problem.

    I have
    (Sheet 1)
    A=1
    B=2
    C=2
    A=4
    B=5
    C=6
    I want(Sheet 2)
    Sum of A=?
    B=?
    C=?
    I Will be highly oblized to you.

    Regards
    Pappu Pattanayak

    1. Hi Pappu,
      You can use below simple command
      Think its in one sheet and result is also published in one sheet
      =SUMIFS($B$:$B$,$A$:$A$,D1)

  25. Can anybody help me with this:
    example:
    Col.A Column B
    States Sales Rep Name
    AZ, CA Blank
    WA,ID,OR Blank
    TX, OK, Blank

    in column A, there's abbreviation of states, like CA, NV, AZ etc...
    I need to conduct a Vlookup that searches the range of these states in (A) and cross references the master Table (on another sheet) to populate Column B. My challenge is that i can't find a Vlookup to search the range of 50 states. Any suggestions?
    thank you

  26. Hello,

    I have sales for about 1000 customers split by various services all grouped into one invoice by split by line item. I need to pull this information and insert the values of each service by customer in a table.
    Source:
    Client 1 Plumbing done on 11th $500
    Client 1 Plumbing done on 24th $500
    Client 1 Work done for Carpentry $1000
    Client 2 Plumbing was done on 25th $1000
    Client 2 Installation done on 10th $250
    Client 2 Carpentry done on 10th $700
    Client 2 Carpentry done on 10th $1700

    I need this info in the below table format (Note, I need sum of each service for each client)

    Clients, Plumbing, Carpentry,Installation
    Client 1 $1000 $1000 0
    Client 2 $1000 $2400 $250

    The above information looks simple, but the description of each item is a sentence out of which I need to pick a "code word" to identify the service. Thank you so much for your help!

  27. =VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

    Working with this formula, but getting the #N/A error.

    Material Material Description 8/1 7/25 7/18
    105939 EBF Org Wild Arugula 2x2lb PL 574 1,350 21,321
    105940 EBF Org Baby Spin - 2x2lb PL 1,741 1,345 1,564
    2,315 2,695 22,885

    1. Need to lookup a material (105940) and bring the qty. on a specific date (7/18). Does this not work because it's trying to reference dates?

  28. I have a excel file where I daily populate cash transactions for several bank accounts. I need to a daily rollfoward with beginning to eOD balances. I prepared a tab for each account and the date in column A and columns B thru J have the cash category. I performed a pivot table that has Account,date,category and total. I need to find a way to drop the data from each bank account into each tab I made so the rollforward can be updated by a simple refresh. I think this can be done using complex if then and combine Hlookup tables but I am not able to drop each dollar amount in its corresponding category by date

  29. Dear Sir,
    I am using Microsoft excel 2007 I want to use vlookup formulla with 3 lacs column but it could not work, it shows error message "invalid reference and this file verson can not contain formulla that reference cell beyond a worksheet size of 236 column (column IW of higher) or 65536 rows".
    I don't understand please help me what I do??????????

  30. Hi, can you help me with this.
    *ship mode minus "H" the order priority "D"
    These are the columns and the data are in words.
    Info tab has all the numbers that corresponds to the H and D columns:

    Order Priority
    1 Low 3
    2 High 5
    3 Not Specified 0
    4 Critical 7

    Ship Mode
    1 Regular Air 10
    2 Delivery Truck 15
    3 Express Air 7

    What formula should I use and can you help me with this one?

  31. Hi All

    Need your help regarding Vlookup, my date contains

    Date
    Product Name
    Opening Stock
    Issued Qty.
    Bal. Qty.

    I need to lookup for two criteria, e.g. data of specific date and product

  32. Example:-

    Sheet one:-
    Serial_numbers
    Abc
    DEF
    GHI
    JKL

    Sheet two:-
    Serial_numbers supplier
    ABC,DEF,GHI Amazon
    ABC,GHI Ebay
    JKL,ABC Flipkart

    I want the details like below

    Serial_numbers supplier
    Abc Amazon
    DEF Amazon
    GHI EBAY
    JKL Flipkart.

    Like this i have around 50k line items.

  33. Hi All,

    I have one question please some one help me to fix it.

    I have two tables one table is having only Serial numbers with removing duplicate
    Another table serial numbers like 10 serial numbers in one column with other details like products suppliers resellers etc.

    Here i want to perform vlookup for unique serial number table and get the supplier details to it.

    please help me some one. thanks

    1. Like this 10 serial numbers i have in some 250 and more than 250 characters length serial numbers column, i want supplier information from this sheet to the original sheet.

  34. Hi Svet,

    It's my first time to visit your site and it very helpful. I need your advise on how to make my project more efficient in a way. I'm creating a monthly scorecard that goals may vary from time to time and has multiple ranges to use. Sample data below:

    Inbound
    0% 15% 30% 45% 50% 65% 75% 85% 90% 95% 98% 100% Weight

    Attendance 80% 82% 86% 89% 90% 92% 93% 94% 95% 96% 98% 100% 15.0%

    SO there's the month, range of score to get (fixed to 0% 15% 30% 45% 50% 65% 75% 85% 90% 95% 98% 100%) and the weight as the last column (say, if agent gets 90% in attendance, he gets 50% * the weight applied of 15%. That weighted score should be summed up with all other KPI weighted score to get the 100% computation for that month. Im sending out the file to your email. :)

    Thanks in Advance!
    Paul

  35. hi i'm trying to use vlookup to pull data from multiple sheets. i'm ok with vlookup within a sheet but how do i read a sheet name from a cell then go to that sheet and use vlookup normally. basically i have sheet with a list of other sheets in it this list changes from an outside data source so i need to read the sheet name and return some data from this sheet.

  36. Oh sorry, I am using VBA...

  37. "How to get all duplicate values in the lookup range" -- your information here is great! I need to know how to do the above using VLookup. Index and Match do not seem to work. I am using Excel 2013. This is my issue:

    Column A has 1's and Null values; Column F has the Data that I need to copy.
    I need All of the Data in Column F as long as Column A is not null.
    Example
    Column A Column F
    cell1 = 1 "West"
    cell2 = Null "East"
    cell3 = 1 "West"
    cell4 = 1 "East"
    cell5 = 1 "South"
    cell6 = Null "South"

  38. I am trying to write a formula that if column B&C is empty it bring nothing. If C has something it must bring what is in column A and if B has something it must bring what is in column A.

    A B C
    L1125A110 L1132A117
    L1130A031
    L1130A066
    L1140A026
    L1145A019
    L1145A031
    L1145A043 LMD322641
    L1145A049
    L1145A051 LMD342154
    L1145A067 LMD193875
    L1145A070 LMD197622
    L1200A147
    L1200A312
    L1211A021
    L1211A039
    L1226A003
    L1230A040
    L1230A046 L1230A154
    L1250A010
    L1250A015 LMD354664

  39. Hello,
    I'm trying to make a meal plan tracker and I'm having a difficult time adding a formula to it.
    One whole sheet is a food list with name, amount, calories, carbs, and fats. The other sheet is where I'm putting the formula and the columns are food, serving, calories, protein, carbs, fats.
    I want to be able to input my food column and have it pull the info from the other sheet.
    I was doing it column by column, this is what I have so far but it won't work:
    =vlookup(vlookup(a2,list!$a$1:$f$41,0,false),calories,0,false)

    pretty much I want to type in chicken and matches chicken from the other sheet, and pulls the values over. Thanks!

  40. I am using the following formula as an array, but am getting blanks. Can you help resolve this please.

    =IF(ISERROR(INDEX(Table_ExternalData_1[#All],SMALL(IF((Table_ExternalData_1[UserName]=$B$1)*(Table_ExternalData_1[comm_datetime]>=$B$2)*(Table_ExternalData_1[comm_datetime]=$B$2)*(Table_ExternalData_1[comm_datetime]<=$C$2),ROW(Table_ExternalData_1[UserName])),ROW(1:1)),4))

    Basically trying to retrieve data using 3 criterias.

  41. how to advance xxl

  42. Maria,
    I have sent in the excel file that I'm developing.

  43. How to do two-way lookup in Excel??
    in this you have showed how we could find second match result for the selected item only LEMON. which is very nice.
    If suppose Same I need find for more than 20 items from the Raw data like more than 500 do I need to enter all 20 items individually or there is any short way for this.

  44. Hi,

    if i have below column or rows,

    upc article no description
    8901725121112 10108458 furnish ang

    is it possible if i want description by upc and by article no in one cell.

  45. Hi,
    I have a Main table with many columns and rows. I have create a bill of matrials taking data from different columns. When I put a formula

    =INDEX(B$4:$B$9,MATCH(F5,$A$4:$A$9,0),MATCH($G$3,$B$3:$D$3,0))/$B$10*$G$10

    it also generate zero (0) values in rows data was not available.
    My question is: I want to generate data with value in initial rows.

    Kind regards,
    Waseem

  46. I have an excel spreadsheet that I'm attempt to do a vlookup or index to get detail of the monthly cost for cell phone to the first tab of a worksheet.
    I have a tab for the details of multiple cell phones by month. it includes base costs, total minutes, text message, GB used, and so on.
    I would like the front tab to pull the current month data from the detail sheet so the data can be reviewed monthly.
    So I have created a tab that has the phone number, then a drop down menu for the month, then I need it to pull the data for that cell for that month.

    This is where I get lost. I can get the data for the first instance but when you change the date nothing happens. I would appreciate any help you can provide.

    1. Hello, Lance,

      To help you better, we need a small sample table with your data in Excel. You can email it to support@ablebits.com. Please add the link to this article and your comment number.

  47. Hi,

    I'm currently looking for a function, that helps me sum up the numbers corresponding to the duplicate data in a different worksheet and this value to be brought to another sheet by vlookup.. In simple terms, a function that sums up and vlookup.

  48. Parameters:
    Name Start Date End Date Value
    A 01-Apr-11 02-Feb-12 2
    A 03-Feb-12 01-Mar-12 3
    A 02-Mar-12 31-Dec-13 4
    A 01-Jan-14 31-Jan-14 5
    B 09-Jan-13 04-Apr-14 6
    B 05-Apr-14 07-Feb-15 7
    B 08-Feb-15 01-May-16 8
    B 02-May-16 01-Jun-16 9

    Name Date Value
    A 30-Apr-12
    A 05-Feb-12
    A 30-Jan-14
    B 07-Apr-14
    B 20-Feb-15

    Please help for for the above value column based on provided parameters.

  49. Dear Sir,

    I am doing vlookup in my time sheet there is four sheets i have done 1 sheet only balance 3 sheets i can't find please help me.

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 :)