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

  1. hello i want to find age (how old as on date) in excel with year and months only, if days in a month is 15 or more , it round off to next month.

  2. Doesn't look like there is a solution to the following: I have a master list of agents. This list shows if they pick up or courier documents. However, the report I must check names against are in 2 different columns. Meaning there is a sell agent column and a buy agent column. I cannot combine the columns. So, I want to check Column 1 and return if the name is found - PU or Courier. If the name is not found, I want to check Column 2 and return PU or Courier. I have searched all over the web, and there is no solution to check 2 different columns and return information. Seems EXCEL should allow you to find this data via a formula solution. If anyone out there has a solution it would be a huge help to publish the steps. I am new to excel, and cannot do visual basic/code. I run this report daily, and right now I am printing the columns and manual checking the list, however there are more than 400 names. Help?

      1. Hey Alexander,

        Thankyou so much for looking into my problem statement.
        I tried your solution and referred to the page that you have mentioned.
        Unfortunately, none of the solution is working for me.

        One thing I want to try from my end and for that could you please help me know the Vlookup formula if the columns are not in series. E.g, in sheet1 I want to check values of column A and B and fill values in column D and I want to match and fetch from sheet2 (Match: sheet1 column1= sheet2 column1, Match: sheet1 column2 = sheet2 column2 and Fetch from: sheet2 column 4 -> sheet1 column4)

  3. Hi there,

    I tired this formula:

    =INDEX('[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!$AI$9:$CM$28,MATCH(B7,'[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!$C$9:$C$28,0),MATCH('[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!$L$5&'[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!$AI$8,'[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!$L$5:$CM$5&'[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!$C$8:$CM$8,0))

    for the below and just can't get it right.

    I have two Excel sheets Construction Payroll Hrs 2023 & Payroll File 2023. In Payroll File 2023, I need the Breakdown section (OT 1.5, OT 2, Holidays, Bank Holidays, Sick Paid, Sick Unpaid) to be filled out based on the week number in B5. This information will be taking from the Construction Payroll Hrs 2023. So number of let's say overtime 1.5 hours will match the Employee ID as well as the specific week i.e. in week 18, employee ID 1 will have the following hrs: 4.5 in OT 1.5, 10 in OT 2, 8 in bank holiday etc. These numbers will change depending on the week. I hope it makes sense.

    Would you be able to help me out? (I couldn't attach the sheets unfortunately).

    Many thanks.

    Lucie.

    1. I used the below formula and it works for the very first cell but doesn't change with the date. Might be a starting point at least?

      =(INDEX('[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!AI9:ME9,SMALL(IF('[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!L5:MI5=B5,ROW('[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!AI9:ME9)-MIN(ROW('[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!AI9:ME9))+1),COLUMNS(B5:B5))))

      1. It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work, sorry.

        1. Hi, thank you for the reply. Apologies, let me explain.

          I have two spreadsheets - one is called Construction - Payroll Hrs 2023 and the other one is called Payroll File 2023. The Payroll File 2023 needs to extract data from Construction - Payroll Hrs 2023.

          I will adjust the column letters and numbers so it makes a bit more sense.

          So let's say that in Construction - Payroll Hrs 2023 I have the following info:

          - Employee ID: A5:A28, with the column heading in A4. Employee ID will always stay in column A.

          - From column B, I will be inputting employee weekly hours, including overtimes, holidays, etc. This sheet is set up for the whole year so there
          will be different amount of hours in every week. Week 1: Basic hours will be in B5:B28, with the column heading in B4. This will change in
          week 2 where the basic hours will be in let's say column I (7 days / week therefore starts from 8th column) - I5:I28 with the heading in I4.

          - Another important info in this sheet is the week number: the week number is placed two lines above the basic hours heading (this will also
          change with the week) B2 for week 1 and I2 for week 2.

          Because I can't have the two excel sheets together, I had to create the Payroll File 2023.

          In this sheet, I have a dropdown list to specify the week I'm looking for: So let's say in B3 I have the dropdown to search for week 1, week 2, etc. Two lines under that, in B6 I will have Employee ID heading and it will list ID numbers from B7 down. In C6 I will have basic hours heading .

          If in this sheet I select week 1, I need it to extract my basic hours etc. from the Construction - Payroll Hrs 2023 - so for week 1 it will take info from B5 to B28 and if I select week 2, it will take the info from I5:I28 etc.

          So I need my basic hours in second sheet to change based on the dropdown (week) selection and Employee ID number.

          I hope this makes a bit more sense?

          Really appreciate your time. Thank you.

  4. I am trying to formulate cell B16 on sheet 1 to look up a value from a table with number ranges in sheet 6 based on data in cell B14 & B15 in sheet 1.

    Please see below example for clarity on the query:-

    B14: 12 (nominal diameter of a bolt)
    B15: 1.75 (pitch)

    B16:-needs to lookup up the nominal diameter firstly in sheet 6 between column A and B

    - the value falls under a number range between two cells. E.g A18 is 11.2 and B18 is 22.4 - 12 falls in between

    - Another lookup function will be needed for the value in B15 sheet 1 - to be matched with column C in sheet 6

    - Once 12 and 1.75 have been matched - the value needed to be displayed in B16 will be displayed in column 4 in the table.

    Which excel function would be most suitable to use in this scenario?

  5. I have a workbook with 10 different tabs each tab has a different supplier price list. I would like to create a quote tool on the first sheet were if you add the part number it will search all 10 tabs for the description and price. would you be able to assist with the formula

  6. I am trying to use value from C2 to look up the array from M2 to Q6 and return column O value. However, the C2 value falls between 2 values in the array. Microsoft Excel's default is to use the smaller value (e.g. if C2 = 38, The look up values in the array are 9, 15, 25, 37, and 50. Since C2 value is between 37 and 50 in the array, Excel returns the value for 37. I want it to return the higher value instead (that is 50 in this example). HOw can I write this VLookup?

    2. The table looks like this and the value from C2 changes. we can have 38, 29, 19, 11
      #(column M) Name Code
      9 QB E12
      15 TE E13
      25 RB E14
      37 WR E15
      50 DE E16

  7. I have two individual spreadsheets with an item number & and address on both. I will need to place a file # on the spreadsheet that does not have it. How ever when I do the Vlookup I receive an error. However the Item number repeats at every change of address.

    for each single address I have 15 items number and I need the File # for each address and item number.

    Please help :(

    1. I am not sure I fully understand what you mean. To understand what you want to do, give an example of the source data and the desired result.

  8. I have an excel sheet that i record the products that i sell. It has the following columns, i.e Quantity, price per unit,total sale, amount paid and outstanding amount. Quantity and price per unit i input manually. Total sale =Qty x price per unit. For payments received i use another excel sheet. How best can i merger payments from payments excel sheet to the column for amounts paid considering that payments are paid in instalments. How best can this be reconcilled without inputing them manually on the amount paid column, retrieving from payments column

  9. I have doubt in vlookup function how to select a "table array" as another cell data
    like =VLOOKUP(D2,AA2,18,0)
    D2 is the lookup value
    AA2 is the lookup range
    18,0) is [range_lookup]) cell of the lookup table
    i have "table array" data in one cell.

  10. Hi I need help.
    Sheet 1 = employee data
    Sheet 2 = leave request data from employees
    Sheet 3 = leave tracker with employees id in rows and dates in rows.

    I want to show in sheet 3 two things, either "plotted leave" or blank.
    'Plotted" will show if the data is equal to the employee id and the leave requested, else it will show as blank.

    Thanks for your help.

    1. Hi!
      I can't see your data, so I can't recommend a formula. To find the employee ID on Sheet2, use the MATCH function.
      The formula could be something like this:

      IF(ISNUMBER(MATCH(ID,.......)),"Plotted","")

  11. I have 2 Excel files.
    Excel 1 contains all the projects (raw data)
    Excel 2 contains all the tasks created for projects in Excel 1
    There are projects with multiple tasks created on them and I want to show all those tasks in one column
    Is that possible?

  12. I've read through these, but a bit lost on the best approach. I'm hoping two sheet vlookup might work.
    sheet 1 has name and appointment date.
    sheet 2 has name, appointment date, appointment status.
    Sheet 2 will have multiple rows for the same name, and different appointment dates for that name.
    i need to pull the appointment status in sheet 2 to match with the name and appointment date in sheets 1 and 2.

    Any suggestions? Thanks in advance!
    FJD

      1. Thanks so much!

  13. Hello I need some help with some excel formula with the data below:
    Column A will have a list of names
    column B will be that person's rest day
    column F will have a list of dates (1 date per cell) for vacation
    column H will have the name who made the request.

    This is what I want to achieve, in Column C, this will tell if a person is on shift, rest day, or on vacation that day. Is this possible?

    Thank you for your help.

    1. Hello!
      Use a nested IF formula on a column to display a value by multiple conditions.
      If I understand your task correctly, try the following formula:

      =IF(B1=TODAY(),"Rest", IF(F1=TODAY(),"Vacation","Shift"))

  14. Hi, If you had Lemons sweet and lemons sour but wanted to return both using this example =INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
    I have changed G1 to "*Lemons*" but it is not working. How do i return a sum of Col D?

    1. Hello!
      If you want to find a partial match of text strings, use the SEARCH and ISNUMBER functions.
      For example,

      MATCH(TRUE,ISNUMBER(SEARCH(G1,A2:A11)),0)

      I hope it’ll be helpful. If something is still unclear, please feel free to ask.

  15. Price update Fruit Name Location Price
    01-Jan-09 Semangka Jakarta 55555
    01-Jan-09 Semangka Bogor 55556
    01-Jan-09 Semangka Bandung 55557
    01-Jan-10 Apel Jakarta 50000
    01-Jan-10 Apel Bogor 60000
    01-Jan-10 Apel Bandung 70000
    01-Jan-11 Jeruk Jakarta 34343
    01-Jan-11 Jeruk Bogor 44343
    01-Jan-11 Jeruk Bandung 54343
    01-Jun-16 Semangka Jakarta 88888
    01-Jun-16 Semangka Bogor 88889
    01-Jun-16 Semangka Bandung 88890
    01-Jun-17 Apel Jakarta 65000
    01-Jun-17 Apel Bogor 75000
    01-Jun-17 Apel Bandung 85000
    01-Jun-18 Jeruk Jakarta 67676
    01-Jun-18 Jeruk Bogor 77676
    01-Jun-18 Jeruk Bandung 87676
    01-Jan-23 Apel Jakarta 125000
    01-Jan-23 Apel Bogor 135000

    How to use vlookup formula if i want to know the price of Apel Bogor at Oct 31th 2020?

  16. how do i select the data filed highest of Wight and highest of Hight

    weight Hight
    1 0.0 148.3
    2 0.0 242.8
    3 0.0 123.2
    4 0.0 135.5
    5 0.0 124.2
    6 64.1 72.1
    7 84.6 105.6
    8 115.4 77.4
    9 128.2 77.7

  17. hi
    i have SKU's customer name and sales to those customer for same SKU in multiple days in one month, Need to Vlookup SKU & Customer and get the total sales qty for each SKU for each customer

  18. Hello,
    looking for some guidance on a task I am struggling with.
    I have to look up the content in coloumn B, based on the content of column A, but cannot seem to get it right.
    My struggle is that I do not always get the desired match, as column B contains multiple matches for the text contained in col A, but I would like to return only matches that contain "%B2B%" (this being part of the text contained in column B).

    Below an example of my data set:

    A B
    x ggg
    x ggg
    x fff-B2B
    y ggg
    y B2B-aaa
    y B2B-aaa

    My desired result would be that for the values that I have in column A, my result always displays the match in column B containing "B2B":
    A = x ---> fff-B2B
    A = y ---> B2B-aaa

    I appreciate any suggestions or examples I may adapt to my case.
    Thank you very much!

      1. Hello Alexander,
        thank you very much for your input!
        I could solve the issue.

  19. I have a sheet, where I wanted to match data in it if there are any discrepancies I wanted to Identify what are those.
    The Sheets typically contains UserIDs in Column and its attributes in rest of the columns like department, first name and last name email address. This file will be manually entered into system by data entry analysts and now I have got the system export file, How can I Identify if there are any discrepancies.

    Sheet1(input file for data entry) : UserID First Name Last Name Department Organization CostCenter OrgUnit

    Sheet2 (system exported data) : UserID First Name Last Name Department Organization CostCenter OrgUnit

    Typically I have rearranged all the columns and now I wanted verify whether the data entered in system is matching with original input file used by data entry analysts.

  20. I have an Excel file with multiple columns. I'm trying to do multiple vlookups in one cell to check each column starting with column A for the SkU, column B and the next few columns up to G to return the total depth for that row (part number in column A) that is reflected In column H. All the part numbers in each column is linked to the part in column A, which are alternate part numbers. Any help anyone can offer will be greatly appreciated. I can send you my excel file, too.

    1. Hello!
      To do multiple-column searches, use a nested VLOOKUP as described in the article above. If you want to find all values in a range, try this instruction: Vlookup to return multiple results in one cell (comma or otherwise separated). I'd recommend you to have a look at our Vlookup Wizard. This tool is part of Ablebits Ultimate Suite that includes 70+ professional tools and 300+ solutions. You can install it in a trial mode and check how it works for free.

  21. I'm trying to use xlookup with multiple criteria across several columns and ~15000 rows of data. The xlookup function returns a value for each row, but the data matches the return array row and not the criteria across columns. For example, data in row 100 in both my table and the return array (source) file is the same, even though the criteria is from row 90 (I don't need all 15,000 rows of data). Do you know why the formula is picking up the data in the row and not from the criteria related to the row?

  22. In a MASTER sheet, I'm having SKU, fulfillment center, and Quantity. need to fetch quantity according to the matching of SKU and fulfillment center in another sheet. because the data of the master sheet will change every time.

    1. Hi!
      You can learn more about VLOOKUP with multiple criteria in this article above. If this is not what you wanted, please describe the problem in more detail.

  23. Hello,
    I am trying to put some data from baseball box scores into an Excel sheet. What I am trying to do specifically is bring in the pitchers for each team into a section of the sheet and then populate another area if a pitcher gets a certain stat (Win, Loss, Hold, Blown Save and Save).
    Each game will have a pitcher get a Win or a Loss but the other three stats may or may not happen each game. The pitcher will be listed with a First Name and Last Name unless they get the certain stat and then the stat will be there along with either how many of the stat or their win-loss record. (Examples: John Smith or John Smith, W (4-3) or John Smith, L (3-4) or John Smith, H (17) or John Smith, BS (3) or John Smith, S (10))

    Here is an example:
    I put the stats in column A1:A5 (W L H BS S) as the lookup value for the stats.
    The pitchers will be copied in column C - Visiting Pitchers in C1:C8 and Home Pitchers in C10:C17. These cells may not all be filled in each game.
    What I want to do is look in both columns and find the stat looked up in column A and put the pitcher's name, stat and/or record from the examples I gave above in the cell that applies to the stat. So I want John Smith W (4-3) from the list to go in cell D1 for example for the winning pitcher. The cell for Win and Loss will each only have one result as well as Save. Hold and Blown Save can have more than one result and I can lost those in multiple cells.

    I hope I have explained this well enough and I can provide more clarity if necessary.

    Thanks for any help you can provide.

    1. Hi!
      This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.

  24. Scenario:

    Sheet 1 is having Names in Column 1 and Row1 is having the Dates.
    Sheet 2 is having Names in Column 1 and Row1 is having Dates.

    I need the formula to return the Dynamic index (Cell).

    Ex: Sheet 1 If the Name and Date match with the Sheet 2 Name and date - Return the particular column values.

    1. Hi!
      Pay attention to the following paragraph of the article above – How to Vlookup in rows and columns (two-way lookup).
      It covers your case completely.

  25. Please help!! Been using Vlookup for a year already the same data over and over and got no N/A nor errors, but this fast weeks we've been experiencing NA. Absolutely sure that formula is correct, lookup_value and table_array references were made absolute correct. Still looking for what may have caused the N/A then correct data, the N/A again cycle goes on like this below: Thank you
    #N/A
    #N/A
    MARIES
    Karmelyn
    Liza
    Ely
    Lara
    #N/A
    #N/A
    #N/A
    #N/A

  26. HI!

    I am struggling with a VLOOKUP and Im not sure why

    I have a column of 18 fields C2:C18

    Coulmn A is filled with roughly 3000+ fields, some of which match what is in the range C2:C18

    Column B is filled with account numbers

    How do I lookup the C2:C18 in column A and return the account number from column B that has a match?

      1. Hey!

        I am looking to see if any of those 18 values from column C match anything in Column A and if they do to return the account number from Column B.

        There might be multiple matches in Column C but a different account number that matches from B

        That formula would only check column A for 1 of the 18 from Column C?

        1. Hi!
          I don't want to guess what you're looking for. Give an example of the source data and the expected result.

          1. Charge (A) Acc number (B) Charge to look for (C)
            XXX1234 Acc 1 CCC1234
            AAA1234 Acc 2 PPP1234
            BBB1234 Acc 3 SSS1234
            CCC1234 Acc 4 EEE1234
            DDD1234 Acc 5
            EEE1234 Acc 6
            FFF1234
            GGG1234
            HHH1234
            III1234

            So I am looking to search the entire of Column A for any result that matches the entire of Column C and return the account number from Column B that the charge is on. I hope that makes more sense, apologies!

            1. =VLOOKUP(C2:C18,$A$2:$B$3111,2,0)

              This is what I came up with but I'm getting an #N/A result for entire sheet, when i know that there are matches. Is there a limitation with VLOOKUPS when searching for multiple criteria in a table?

              1. Hi!
                Note that the VLOOKUP function can only look up one value. You want to search multiple values at once. To not display an error message, use the IFERROR & VLOOKUP function. Please read the VLOOKUP manual carefully.

                =IFERROR(VLOOKUP(C2,$A$2:$B$3111,2,0),"")

  27. Hello,
    I am trying to write a formula that looks at three criteria and if all three criteria are met return a name.

    I have the names in cells b17 through b36.

    The first match I need is in cells e17 through e36. In these cells the number can be <= 2
    The second criteria is in cells f17 through f36. In these cells the numbers can be 9.
    If all three of these criteria falls within the range given, I need the result to be the name listed in cells b17 through b36

    I have followed the match index but it’s not working for me.

    If you can let me know if this can be done I am great full.

    Thanking you in advance, Chuck Vaughan

    1. Hi!
      Please clarify your question. Should the condition be true for all cells in the range or just one of them? Is the result of the formula also a range of cells?

  28. Great source of how to use Lookup functions.
    Is there any way to make the lookup_array dynamic or a computed value (without using named ranges that are defined)? I've tried using the indirect function as you have but in the form of
    =VLOOKUP(lookup_value,INDIRECT(B2)&":"&INDIRECT(D2), columnIndex, rangeLookup)
    where B2 and D2 are the corner points of the desired array (in the form of $f$10 and $p$100)
    array 1 $f$10:$p$100
    array 2 $q$10:$aa$100
    array 3 $ab$10:$al$100
    etc...
    Using defined named ranges creates additional workload and using a fixed lookup_array creates a massive array.

    1. sorry, I was using the Indirect function incorrectly, but using the equation
      =VLOOKUP(lookup_value, B2&":"&D2, columnIndex, rangeLookup)
      just gives me a '#value' error because apparently B2&":"&D2 is evaluated as the string "$f$10:$p$100" and not the range $f$10:$p$100.

      1. my apologies again, after some additional trial and error the following works - but thanks for your tutorial it definitely helped in solving my problem.
        =VLOOKUP(lookup_value, INDIRECT(B2&":"&D2), columnIndex, rangeLookup)

  29. Name/date 7/22 7/23 7/24
    name1 65 55 22
    name2 0 22 19
    name3 2 59 0

    Hi pls refer to the table I want to know when I select date I want to get cell values 1st highest to low then i need to get corresponded row value in front of that number

    Say I Select 7/24

    the result should be:
    22 name1
    19 name2

    1. Hi!
      The following tutorial should help: Excel INDEX MATCH MATCH and other formulas for two-way lookup.
      Please try the following formulas:

      =LARGE(INDEX(B2:E4,, MATCH(H2,B1:E1,0)),1)

      =INDEX(A2:A4,MATCH(LARGE(INDEX(B2:E4,, MATCH(H2,B1:E1,0)),1), INDEX(B2:E4,, MATCH(H2,B1:E1,0)),0))

      where H2 = "7/24"
      The LARGE function with an argument of 1 specifies the highest value in the range. For the second value, change 1 to 2.
      I hope it’ll be helpful.

  30. 2 sheets with addresses, trips sheet and jobs sheet. Trips is from fleet software tracking address of vehicle. Jobs sheet is job address and job data including job#. I need a column on trips sheet that looks to the job sheet addresses, finds the clise match and returns the job #.

    Have tried vlookup and indexmatch.

  31. Does the author issue any Email Seminars or thoughts? She is truly one of a kind - great Excel Seminars and would truly appreciate being advised of any & all seminars she might offer.

    Thoughts?

    Being researching Excel seminars for the last few decades & have found she is the leader - best

    1. Thank you for your kind words, Waldo. I do not run any email seminars. You can find all my Excel articles on this blog.

  32. I have inventory spreadsheet from month to month. The ending inventory of the previous month is the beginning inventory for the current month. Sample Formula for the current month =IF(ISNA(VLOOKUP(V2,Mar22!C:D,2,FALSE)<=0),0,(VLOOKUP(V2,Mar22!C:D,2,FALSE))). The formula works, however, I want the negative balance to show as "0" for the following month. Please help. Thank you

    1. Hello!
      Add one more condition to the formula with a nested IF function. I can't check the formula that contains unique references to your workbook worksheets.

      =IF(ISNA(VLOOKUP(V2,'Mar22'!C:D,2,FALSE)),0, IF(VLOOKUP(V2,'Mar22'!C:D,2,FALSE)>0, VLOOKUP(V2,'Mar22'!C:D,2,FALSE),0))

  33. Hello,

    I am attempting to retrieve certain data using a unique identifier (123456), points from another sheet onto the main one I need the data on though there are multiple data points.
    This the formula I am using but keep getting an error:
    =VLOOKUP(A2,INDIRECT("A"&(MATCH(A2,Gradebook!$A$2:$F$2891,0)*ROW(Gradebook!A1:A2891))&":M2891"),6,FALSE)

    One tab in the workbook is titled Main and these are the data points (below):
    Student ID First Name Last Name Grade P1 Course P1 Mark P2 Course P2 Mark P3 Course
    123456 Student Test 9
    Which I am trying to pull the data points from tab titled, Gradebook, that contains the data points below
    Student ID Student Name Course Periods Mark Perc
    123456 Test, Student Literature 12 P1 C 72.33
    123456 Test, Student Chemistry P2 F 57.28
    123456 Test, Student Geometry P3 D 60.53
    123456 Test, Student Theater P4 B- 80.25
    123456 Test, Student Ethnic Studies P5 B- 80.35
    123456 Test, Student Fitness P6 C+ 78.92
    Which formula I can use, how can I pull the data points from Gradebook to paste onto the Main tab under each column?

    Thank you!

      1. =INDEX(Gradebook!$G$2:$G$2891,SMALL(IF($A2=Gradebook!$A$2:$M$2891,ROW(Gradebook!$A$2:$M$2891)-1,""),1))

        I found this formula and it pulls the data I need but it is possible for it to pull data from a column based on data from another column?

        For example:
        Student ID 123456 has 3 columns of data
        Column A: PE
        Column B: Period 1
        Column C: A+
        How can I pull from any data point from Column A when column B contains specific text such Period 1, Period 2, etc?

  34. I been working to recreate this seminar and have a few questions:
    1)How to Vlookup and return multiple values in Excel - utilize INDEX, SMALL & ROW functions section Formula - {=IFERROR(INDEX($C$2:$C$11, SMALL(IF($F$1=$B$2:$B$11, ROW($C$2:$C$11)-1,""), ROW()-1)),"")}

    If the cell containing this formula is C250 how does the above change? Should the "ROW()-1" become "ROW()-250? Can't get this to work

    2)Name Range "Product" in one of your sections you state the range for Product as B2 It should shown as B2:B11

    Thoughts?

    Your seminars are one of the best if not THE BEST - many thanks Outstanding & very educational

    1. Hi Waldo,

      1) The generic formula is this:

      IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range ) - m ,""), ROW() - n )),"")

      Where:

      - m is the row number of the first cell in the return range minus 1.
      - n is the row number of the first formula cell minus 1.

      Assuming both the first cell in the return range and the first cell containing the formula are in row 250, you formula may look something like this:

      =IFERROR(INDEX($B$250:$B$260, SMALL(IF(D$249=$A$250:$A$260, ROW($B$250:$B$260)-249,""), ROW()-249)),"")

      For the detailed explanation, please see How to Vlookup multiple matches and return results in a column.

      2) Can you please specify the section's name? Cannot find it.

    2. Your Section - How to do multiple Vlookup in Excel (nested Vlookup) - 2 subanalysis to VLOOKUP 3rd file

      Shows the "Products" range as D3:E3 believe it should be D3:E10
      Shows the "Prices" range as G3:H3 believe this should be G3:H10

      Thoughts?
      Thanks

      1. You are absolutely right, fixed. Thank you for pointing out that mistake!

  35. I am trying to use this =VLOOKUP($A4,Data2!$A:$AC,H$1,FALSE) to pull forecast for multiple months from data file but this does seems to be working, could you please walk me through to use this formula appropriately ?

  36. I'm looking for a solution to work around using vlookup + vlookup
    The data set is something like this:
    10
    20
    31;32
    40
    The current idea is to insert enough columns to separate all items, then iferror(vlookup,,,),0) + iferror(vlookup,,,),0) + iferror(vlookup,,,),0) to sum all instances, or manually overwrite the single vlookup on the lines where multiple items are needed

    1. Hello!
      Sorry, I do not fully understand the task. Could you please describe it in more detail? What result do you want to get?

  37. Hi. Can you please tell me what exactly do the following formulas yield. PLEASE!

    =VLOOKUP(C2,M2:N180,2,0)

    =VLOOKUP(C9,M:M,TRUE,FALSE)

  38. Hi There

    i worked in logitics company where i need to to find Vlookup 1,2 and 3 occurence value that are in same column against in a order and want answer in column 1, 2 and 3 and then if blank choose other option kindly help me , to fix this

  39. I have an excel data like following, i want to Securitate only work completed line items in another work sheet, which formula we can use in VLOOKUP

    SL.NO. QTN STATUS QTN REFE NO.

    2 PENDING CS-QTN-06-21-0002
    3 WORK COMPLETED CS-QTN-06-21-0003
    4 REJECTED CS-QTN-06-21-0004
    5 WORK COMPLETED CS-QTN-06-21-0006
    6 WORK COMPLETED CS-QTN-06-21-0007
    10 PENDING CS-QTN-06-21-0013
    11 WORK COMPLETED CS-QTN-07-21-0005
    17 PENDING CS-QTN-07-21-0017
    18 PENDING CS-QTN-07-21-0018

  40. HI Team
    im using the below formula from vlookup-in first cell i entered fileExcel -3201
    and i need to change each cell for example-3201,3202,3203.how to do it

    find below for clarify

    =VLOOKUP($B64,'C:\Users\Desktop\Excel 3201\[TOP BOTTOM KEY_16.12.2021_3801.xls]Report'!$A$14:$B$20,2,0)

    1. Hello!
      Here's how to get a link using the formula:

      =INDIRECT("'"&B2&"["&A2&"]"&C2&"'!$A$14:$B$20")

      A2 - file name (TOP BOTTOM KEY_16.12.2021_3801.xls)
      B2 - File folder name (C:\Users\Desktop\Excel 3201\)
      C2 - sheet name (Report)

      In order for the link to work, the file must be opened, otherwise, you must use a VBA macro.
      You can read more about using the INDIRECT function in an article on our blog.

  41. Hi Svetlana,

    I am new in Excel and I would like to use codes.
    I would like to assign A=1, B=2, C=3, D=4, E=5, F=6, G=7, H=8, I=9, J=0.
    if I would enter DJ, the value would be 40
    If I would enter FCB, the value would be 632
    Your help is appreciated.

    1. Hi!
      To change a letter to the corresponding digit, you can use the SUBSTITUTE function.

      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"C","3"),"B","2"),"A","1")

      You can continue this formula with other letters and numbers.

  42. Hi I'm try to make a vlookup with this information .
    Category Indicates the category the item belongs to. Item categories are: “Bread & Bun”, “Cookie”, “Cake” and “Pastry”. Each ItemID beginning with the letter B belongs to the Bread & Bun category; those starting with K belongs to the Cookie category; those starting with C belongs to the Cake category; and those starting with Y belongs to the Pastry category.

  43. Is there a way to perform a VLOOKUP and have it be case sensitive? For example, our ID's are 11384fMY15KIv and there may be one that is 11384FMY15KLv. Each one is different but the VLOOKUP function will return just one.

  44. Hi, I am curious to know what will be the formula be if we are looking for 3rd occurrence instead of 2nd occurrence?

    Understand that from the formula shown above, +2 = +1 to exclude the first instance and +1 to exclude row 1 with the column headers

    =VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)

    If the 3rd occurrence is 5 - 6 rows below 2nd occurrence, what will be the formula?

    1. Hello!
      This formula finds the third match in the VLOOKUP search:

      =VLOOKUP(E1,INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1 + MATCH(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0) +1+ROW(A1))&":A11"), 0)+ROW(A1))&":B11"), 2, FALSE)

      1. hi this work in practise sheet and but able to help in real time solution

  45. I wanted do vlookup in the main file from the different excel files. I need information in the one column in main file. I.e if booking pertains to CC then only CC files needs to be referred in Vlookup and similarly if booking pertains to other type then respective file should be referred for vlookup.

  46. Hello,
    I used the vlookup and it works but now i cant move the column to another excel. returns #REF!
    please HELP ME!!

  47. I have been trying to use a vlookup in my spread sheet, but i cannot seem to get it to work correctly. Maybe i need something else?
    Data columns on my data sheet are:
    (A)Truck #, (B) Date, (C) Miles Driven and (D) Driver Name

    What I need to do is:
    look up the truck # by date and return the driver name

    Any and add suggestions are greatly appreciated.

  48. 0

    I don't know if this is even possible I am going to try to explain it the best I can, I don't know if the "IF" function is the correct thing to use here...

    I want to Vlookup From a list in Column A but what I want to look up is dependent on what is in Column B

    I don't know how to put this in a formula or even if you can:

    If B3=R then =VLOOKUP(C3,Sheet1!$B:$R,11,false) If B2=L then =VLOOKUP(C3,Sheet2!$B:$R,11,false)

    Is this possible? Any suggestions how to approach it, is there a better function that I don't know of yet? Thank you

      1. THANK YOU! This worked! I was on the right track, saved me a few more hours of trial and error

  49. Hey can u plz tell... is there any shortcut to go to the cells on which our vlookup function is dependent if those cells are in a different sheet.

  50. Hello,

    Im trying to find a vlookup that will return when a value is found in the column (when there is no blank). For example:

    item
    item
    item value1

    VLOOKUP should return "value1", not 0 or blank. How can I accomplish? Thank you.

    1. Hello!
      VLOOKUP function will not be able to solve your problem. Use INDEX + MATCH in formula:

      =INDEX(B2:B20,MATCH(TRUE,(A2:A20="item")*(B2:B20 > 0) > 0,0))

      You can learn more about INDEX + MATCH in Excel in this article on our blog.

      1. Unfortunately this only returns me #N/A. Is there another way?

        1. Found the issue. It needs to be shift + ctrl + entered :)

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