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

  1. 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?

  2. 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

  3. 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?

  4. 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"))

  5. 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)

  6. 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

  7. 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!

  8. =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?

  9. 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

  10. 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??????????

  11. 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?

  12. 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

  13. 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.

  14. 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.

  15. 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

  16. 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.

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

  18. "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"

  19. 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

  20. 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!

  21. 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.

  22. how to advance xxl

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

  24. 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.

  25. 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.

  26. 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

  27. 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.

  28. 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.

  29. 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.

  30. 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.

  31. Dear Sir,

    I Want to update leave code in attendence sheet according to leave trransaction on the basis of Employee code and respective dates from start date to end date remaining values in main sheet should be constant.

  32. need your help on this. I want to vlook up for the specific number from another worksheet but the leftmost value is the combination of samenumber and some text I want the value in the 4th column. pls help

  33. great site......

    could you help me with the below query.....

    I am doing vlookup, my ref column will have duplicate but I need all their corresponding items in one single cell line by line... can u help me on this....

    1. Hello Sudhakar,

      Thank you for your feedback.

      I'm sorry, but we don't know of a simple way to get all values in one cell. You may need to use VBA for this task.

      You can get all values in different cells, please see the "Get all duplicate occurrences of the lookup value" section for a detailed description.

      Then you can use the Concatenate function to merge all the values you get into one cell. Please see this post for more information:
      https://www.ablebits.com/office-addins-blog/excel-concatenate-strings-cells-columns/

  34. I have 4 columns of Reg. No. Name Subject and Grade. I want to return the grade of specific subject of a student how can I use the vlookup formula.

  35. I love this Page.... I am a self learner and got lot of help from this page....

  36. I have a large data for groups for e.g.

    GRP_1 GRP_2 GRP_3
    98465 5521 65466
    65468 6663 6541
    68465 6545 36541
    65466 8466 6541
    65466 9548 65666

    and I want to create a list of products and which group it belongs like

    Product Groups
    5521 ??
    6541 ??
    6541 ?
    6545
    6663
    8466
    9548
    36541
    65466
    65466
    65466
    65468
    65666
    68465
    98465

    Thanks

    1. Hello, Ashwin,

      Please try this formula:
      =IF(ISERROR(MATCH(E2, $A$2:$A$6, 0)), IF(ISERROR(MATCH(E2, $B$2:$B$6, 0)), IF(ISERROR(MATCH(E2, $C$2:$C$6, 0)), "",$C$1), $B$1), $A$1)

  37. Hey,

    I loved you instructions! I have one question.
    I am using INDEX MATCH to find a cell from another workbook and place the name of the cell near it. For example:

    =INDEX('[Schedule 11.30.15 Hotschedules.xlsx]Sheet1'!$A$8:$A$75,MATCH($B$10,'[Schedule 11.30.15 Hotschedules.xlsx]Sheet1'!$C$8:$C$75,0))

    Which works fine. But sometimes the "time" in the cell changes. For example $B$10 is "6:00 AM - 2:00 PM [Breads Sales]" but sometimes I use an employee that is only "6:00 AM - 1:30 PM [Breads Sales]". How can i make INDEX MATCH use 2 lookup values incase the first one fails?

    1. Hello Argenis,

      Try the IFERROR function that allows you to return another specified formula if the first one returns an error:

      =IFERROR(INDEX('[Schedule 11.30.15 Hotschedules.xlsx]Sheet1'!$A$8:$A$75,MATCH(_$B$10_,'[Schedule 11.30.15 Hotschedules.xlsx]Sheet1'!$C$8:$C$75,0)),

      INDEX('[Schedule 11.30.15 Hotschedules.xlsx]Sheet1'!$A$8:$A$75,MATCH(_$B$11_,'[Schedule 11.30.15 Hotschedules.xlsx]Sheet1'!$C$8:$C$75,0)))

      https://support.office.com/en-us/article/IFERROR-function-c526fd07-caeb-47b8-8bb6-63f3e417f611

      1. Thank you, It worked wonderfully!!!

  38. Below was the table my scope of search

    Customer Name Product

    Brown Apples
    HILL CHock
    Brown Sweets
    Acey Lollypop
    Wolf chikky
    Brown Biscuits
    Hill Alapino
    Wolf Jelly
    Hill gems

    Search Cell in the same Sheet like the following

    $G2 $H2

    Dan Brown Apples
    Sweets
    Biscuits

    $G$5

    Hill ????
    ????
    ?????

    wolf

    I had used the following formula to get the first set of search:
    =IFERROR(INDEX($C$2:$C$16,SMALL(IF($G$2=$B$2:$B$16,ROW($C$2:$C$16)-1,""),ROW()-1)),"")

    I like some help to expand (or) new formula to list all the duplicate values with respect to the string mention within the Left side, instead of hardcording the right side formula....that is $G$2

    Thanks for looking and trying to help me out...!!!

  39. hello,

    a have a question based on your example please:

    if a have the next situation:

    Dan Brown A B
    Dan Brown C D
    Jeremy Hill T I
    Dan Brown R T

    and I want to have the next result into another sheet :

    Dan Brown A B C D R T
    Jeremy Hill T I

    How can I do that?

  40. it seems that my formula isnt working ..i use your formula as refference but it always shows nothing.. anyone please help

    =IFERROR(INDEX(E5:E11,SMALL(IF(E2=E5:E11,ROW(F5:F11)-1,""),ROW()-3)),"")

    1. Lineth,

      Please make sure you press Ctrl + Shift + Enter to enter the array formula correctly.

  41. I am quite poor on excel and can do simple VLookups. I need to do one that I think should in corporate an If function but do not know how. I need it to Vlookup a selection of codes and return the rate from column 2. However for 1 code I need it to go to one list for the answer, for all other codes I need it to go to another list for the answer. e.g I have 10 carrier codes 9 of the codes have a fixed rate - easy but one of the codes has a different rate for every item. So for the 9 with a fixed rate easy however how do I tell it that if code is this odd one go lookup on this list but if not go lookup on that list? I have played around for days trying to work it out. hope that this makes sense

  42. Hi Alireza,
    for post 54. you can use the following array formula without sorting your first column:
    =max(if(b2:b15=e2,c2:c15))
    use CSE.
    Good luck.

  43. Dear all am looking for some help

    I have a cell with code in it and than a seperated sheet whit the same codes and some devided lanes exp
    cell M17 = TYO
    in sheet 2 I have a range A24 untill B 39 were TYO is mentioned 4 times in A and the in B there is TYO+ each time a different explanation

    now I would like to find a formula to place in sheet one M17 is TYO that the various explanations apeare

  44. Hi - I have a table with 3 columns: Course Name, Credits and Category
    IE
    100W 3 GE
    117a 4 GE
    17a 3 Comp
    56 3 Surveys

    I have a worksheet with the following columns: Instructor, Course Name, Total Credits

    Cindy Miller 17a 3
    Shannon Baer 17a 3
    Bill Jones 56 3

    I created a lookup table to calculate the total credits based on the selected course name and looking at the credits associated with same in the table.

    This works fine.

    Now I want to determine the quantity of courses for each Category.

    So in the example of Cindy, Shannon and Bill it would look like this:

    Category # of Courses
    Comp 2 (since course 17a is a category Comp and Shannon and Cindy are teaching)
    Surveys 1 (since course 56 is a category Survey and Bill is teaching)

    Can't wrap my head around how to get the count of courses offered by category.

    thanks for your help

  45. Dear Sir/Madam
    My query is that how I can use ">" & "<" greater than & Less than in single formula in excel sheet by solving the problem.
    QN.:Find the value greater than 250 and Less than 800.
    Example:

    Name Amount Age
    Bill 256.95 56
    Joe 125.63 22
    Mary 25.66 59
    Dave 548.00 21
    Frank 489.32 48
    Sue 500.25 19
    Hillary 368.59 15
    Kate 901.56 25
    Aleisha $99.95 33

  46. I have two spresdsheets - one having a customer name in column A and product name in column d and another spreadsheet having net price for the same criteria. The net price are different for same product for diffferent customers. I am trying to bring the net price to the first spreadsheet. What is the formula to use? Thanks

  47. Great help to me as a novice. Is there anyway you could assist with the following problem;
    In a cell I have a date which can be changed eg, Aug 2015, Sep 2015, Oct 2015 etc. This in essence represents the sheet name containing the main look up table.Is there a variant of the the vlookup formula below that:
    Updates the formula with the date (sheet tab name) based on the selection in the date cell?
    EXAMPLE: If Sep 2015 selected in date cell, then return the formula ,=VLOOKUP(A6,'SEP 2015'!$B$5:$L$287,7,FALSE).
    If Oct 2015 selected in date cell, then return the formula,=VLOOKUP(A6,'OCT 2015'!$B$5:$L$287,7,FALSE).
    Any help would be greatly appreciated thanks!

  48. I have two tables Main table and Vlookup table. Vlookup table as "place_table". Vlookup table has three sheets of seperated places sheet1 as Mumbai, sheet2 as Delhi, sheet3 as Chandigarh.In main table I am using vlookup formulas seperately =vlookup(b2 ,[place_table.xlsx]Mumabai!1:1048576,2,0), =vlookup(b2 ,[place_table.xlsx]Delhi!1:1048576,2,0), =vlookup(b2 ,[place_table.xlsx]Chandigarh!1:1048576,2,0). In main table I have seperate columb which shows places Mumbai, Delhi and Chandigarh. How can I use this columb to applying only one formulas to all

    for one formula for all three sheets.

  49. Hi again:)

    After a night thinking about the problem above I have answered my own question.

    To increment the "-20" I placed the following in an unused cell on row 20, (in my case this cell is in a column that is normally hidden).

    "=row()"

    this shows the row number & updates every time a row is added above it.

    I named this cell "row_increment"

    Then I adjusted the code above:-
    ),ROW()-20)),"Error")}

    to read:-

    ),ROW()-row_increment)),"Error")}

    So every time I add a new row ABOVE row 20 [=row()] automatically updated the -20 to -21, -22 etc
    My table no longer looses the top row

    I hope this makes sense to others.

    SteveN

  50. Hi

    I've copied and modified your example above for finding duplicate values.
    my code:
    '{=IFERROR(INDEX(expedite_report!$H$8:$H$32000,SMALL(IF($B$21=expedite_report!$A:$A,ROW(expedite_report!$H$8:$H$32000)-14,""),ROW()-20)),"Error")}

    This works very well thank you :)

    I have an issue tho'
    The worksheet is growing all the time with new rows being added which then impacts "ROW()-20". The $B$21 increments as new rows are added but the "-20" does not & I'm loosing results off the top of the array.

    How can I modify this code such that the "-20" increments when a new row or rows are added?

    SteveN

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