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

  1. i have patient data list in from column A to D ( example Colum A, MR No, Colum B, Name, Colom C, Medicine, Colom D, Labs, C etc)

    I need your help How to VLOOKUP and return multiple values when I enter the MR no in cell and get others information from colm b to c

  2. Hello,

    I have a difficulty to use any formula, for a very simple task. I always get an error like #VALUE or #N/A as result and I do not understand why.

    In Table 1 , there is in C2 the PlantName W01 and in A44 is the Process Name = Casting, and I want to get from Table 2 the corresponding ScrapCost matching to these 2 values in the Table 1, in the D44 Cell. I used VLOOKUP, INDEX/MATCH, and I always get an error and I do not know why?
    The Table 2 has several Columns with O4:O157 = PlantNames , from Q1:Q25 ProcessesNames (repeating for each plant until Cell Q157), and corresponding ScrapCost is in T4 :T157 (for each PlantName and each ProcessName).
    Can you help me ?

  3. Hello I Have Data Like One Lookup But Different Cells Example One Sheet Lookup Attendence And The Data Different Cells Like Colum Number 2,3,4,5,6,7,8,9,10 So How Can Lookup One Click Atten.But Same At Is Work For Drage

  4. Hi I need to add a price against multiple duplicate part numbers, the Vlookup function works so far, then the formula goes wrong as the column index number starts to run sequentially. example below. So the Vlookup works and it's pulling across the cost correctly. But there are 30 duplicate part numbers following this one, and this is where vlookup goes wrong. rather than looking at the same cell from the sheet 1 data for each duplicate it starts to look at the cell below, so doesn't return a correct value.

    Branch Part Qty Picked Cost
    AYRE AGLBTG325 1 1 £4.99

    =VLOOKUP(B367,Sheet1!A56:B515,2,0)

    Sheet 1 Data
    Part Cost
    AGLBTG325 4.99

    Branch Part Qty Picked Cost
    AYRE AGLBTG325 1 1 £4.99
    BIRS AGLBTG325 1 1 #N/A

    Vlookup formula for next duplicate down

    =VLOOKUP(B368,Sheet1!A369:B516,2,0)

    As you can see it's looking sequentially on sheet1 rather than looking at cell A56, so the next cell down is now looking at A370. How do i get it to stop doing that? Any help would be appreciated as there's 149 part numbers but a total of 1474 duplicates I need to cost up.

  5. Hi. I am trying to do a VLOOKUP that searches for multiple lookup values. For example, I know this syntax doesn't exist, but I want to achieve something like this =VLOOKUP(H4 OR H5, A1:B10,2,). My return values in this case are all numbers, so ideally I'd like it to look for any values matching cells H4 or H5, then sum all corresponding values and return. In this case there may be a value only in H4, only in H5, or both. Is this possible??

  6. Hi, I would like to ask for a formula for a scenario below:

    If enter a Name in B2, output will be as below:

    B2: Name
    B3: Address
    B4: Age
    B5: Contact Number
    B6: Email Address
    B7: School

    thank you for the help

  7. Hi, i am trying to use vlookup get 2 results within one request - e.g If the data if found within the array then the resulting cell is to be multiplied by a specific cell, but if the data is NOT found then the resulting cell should be multiplied by a different cell
    =VLOOKUP(A1,$B$1:$B$5,1,0)*$C$1 - works if the condition is true (the match is found) - but how do i include the same equation withen the formula if the condition isnt true (the match isn't found) - which i would them want it to be then multiplied by a different cell e.g $c$2

    1. Hi! I don't really understand what you want to multiply if no value is found. To display some value instead of an error message, use the IFERROR function. For example:

      = IFERROR (VLOOKUP(A1,$B$1:$B$5,1,0)*$C$1,C2)

  8. OMG. I had a huge list of people that had different "interests" and "expertise". Each category (interest and expertise) had about 15 subcategories that people could select from. In querying my database I could easily pull a list of people, but the data returned had a separate row for each person/category/subcategory. Not uncommon for a database query.

    But, I was preparing data for a mail-merge, so I needed to create a file that had each individual person listed on a single row. Then I needed to create columns for each category/subcategory pairing. Getting the names for the mail-merge was easy; I could just pull the name from the database dump, paste into a new sheet, and then remove duplicates. But figuring out how to match the info in the category/subcategory columns was crazy hard since there needed to be 3 things that matched: Name, Category, Subcategory.

    The XLOOKUP option worked perfectly. Still not exactly sure what all the items are (like why we use "1" as the lookup_value) and the Office 365 sort features were slightly different than what was used here. But between the great explanation here combined with the prompts in Excel for the last few items, made it workable. Then it was just cut/paste formulas into various columns and tweak the category/subcategory options for each cat/subcat column.

    Thank you so so sooooooooo much.

    Sample for clarification:
    Converting from - (I added the Return Value column to my database dump for insertion into the modified mail-merge table)

    Individual Category Subcategory Return Value
    Person X Category 1 Subcategory 1 X
    Person X Category 2 Subcategory 1 X
    Person X Category 2 Subcategory 2 X
    Person Y Category 1 Subcategory 1 X
    Person Y Category 2 Subcategory 1 X
    Person Z Category 1 Subcategory 2 X
    Person Z Category 2 Subcategory 1 X

    Into - (for mail-merge purposes)

    Individual Category 1/Subcategory 1 Category 1/Subcategory 2 Category 2/Subcategory 1 Category 2/Subcategory 2
    Person X X X X
    Person Y X X
    Person Z X X

    1. Hi! Unfortunately, your request goes beyond the advice we provide on this blog. 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.

    2. LOL. Okay, the "extra" spaces to try and make it look table-like didn't come through, but hopefully you get the idea. And everything did work swimmingly.

  9. I have a worksheet with different file numbers for the same account

    Account number is in column A and file numbers in B

    Please give me a formula where it will give me all the multiple values in column B for an account number in column A

    Thank you

  10. please tell me the formula for different columns data to lookup for same values . for ex : order id AB022365 , column heading 1 order status( delivered/undelivered , colun2 (complaint), column 3( branch)and so on . but formula must work together .

  11. Hello, I am looking for formula where I have a row on product with multiple information in columns. I have to base information from two column and return back value from column 1. For example:

    Houses Cars Office
    Matt 3 2 2
    Venkat 4 1 3
    Lars 2 3 6
    Sam 8 4 5

    Output I want to know person with 2 houses and 1 car
    I want to know person with 3 houses and 4 cars

    How can this be done?

  12. I'm looking for a formula that will look up a date in column A, then lookup a name in column C, then look up a category from row 1, and return the value when all three has been met.
    I have tried using the following =INDEX(AU2:DJ139,MATCH(C2,AU2:AU5000)*(B21,AW2:AW5000)*(F20,AU1:DJ1),0) but get #N/A returned.
    Any suggestions as to what formula will look up all three and return the value would appreciated.

  13. Good afternoon,
    I am unable to find a formula to produce a correct value in a spreadsheet. I have two worksheets (DR and PI).

    DR contains complete records and PI contains the records I am reviewing. I need excel to find PI cell value of A9 and PI cell value of I9 in a single row in the DR raw data, and when those values match return the value of DR column 11 to PI.

    1. Hi! To search for two values, use the INDEX MATCH functions. However, your information is not enough to give you more accurate advice. In which cells will you search for these values? To understand what you want to do, give an example of the source data and the expected result.

  14. I am using the VLOOKUP function to search for values in one workbook (Workbook 2 cells: F513 = 515,600.05 and cell F518 = 96,560.46) 6 columns to the right of column A, based on multiple values in another workbook, within ONE cell (Workbook 1 cell A15 = 401-05-0000, 403-01-0000. I would like the results to be added together and the result placed in one cell (Workbook 1 cell G15. How should I modify the VLOOKUP function below at the red arrow to do this?

    =VLOOKUP(A15,'[BVAR Balanza julio 2023.xlsx]Balanza de Comprobación'!$A$387:$F$834,6,FALSE)

    Thank you
    David

    2. Sorry, ignore "at the red arrow to do this?". I could not post a screen shot. I just want to know how to modify the VLOOKUP to accomplish the above.

      Thank you,
      David

  15. How many sources of importrange i can use for vlookup?

  16. HI I have two sheets.

    Sheet 1 is where i want to see the result (Allocated time) and Sheet 2 is the source. There are two criteria to look at 1 is the country 2 is the service provider 1-5. Main goal is to catch the number of hours assign for each service provider in a given country.

    Sheet 1
    Belgium Provider 1 Allocated Time based on Sheet 2
    Belgium Provider 2 Allocated Time based on Sheet 2
    Belgium Provider 3 Allocated Time based on Sheet 2
    Belgium Provider 4 Allocated Time based on Sheet 2
    Belgium Provider 5 Allocated Time based on Sheet 2

    In the source sheet 2, you'll notice that the provider is not in sequence like in Sheet 1

    Sheet 2
    Belgium Provider 3 Allocated time
    Belgium Provider 5 Allocated time
    Belgium Provider 2 Allocated time
    Belgium Provider 1 Allocated time
    Belgium Provider 4 Allocated time

  17. Hi,
    I have a query how to vlookup on multiple conditions.

    my data
    I have emp id in column A, emp details in Colom D and values in column E
    my qurey is I want to vlookup for column A 1st and match with column D and get the results of E in to my results cell.
    column D have multiple rows like basic pay, DA, HRA etc column A have emp id

    here is the sample data

    1010408 RAMESH RUPIREDDY Personnel#: 1010408 BASIC PAY 1,85,430.00
    1010408 RAMESH RUPIREDDY Name: RAMESH RUPIREDDY Basic GPF Arr 7,57,185.00
    1010408 RAMESH RUPIREDDY Desig ADE STAGNATION PAY 12,240.00
    1010408 RAMESH RUPIREDDY Seat No: CPRS-S2-1 Stagnation Pay GPF -52,860.00
    1010408 RAMESH RUPIREDDY BANK: SBHY0020138 Stagnation Pay EPF 69,779.00
    1010408 RAMESH RUPIREDDY A/c No 52135288942 D.A. 11,655.00
    1010408 RAMESH RUPIREDDY Net Pay: 181559 D A(GPF) Arr -4,17,824.00
    1010408 RAMESH RUPIREDDY PPO No D A(EPF) Arr 2,391.00
    1010408 RAMESH RUPIREDDY H.R.A 21,000.00
    1010408 RAMESH RUPIREDDY HRA Arr 78,001.00

    Request to provide the me formula for this

    1. Hi! Unfortunately, this information is not enough to understand what you need. Specify what results you want in column E for each row of your data sample. Describe your task in more detail.

      1. my day is like this

        Personnel#: 1010408 BASIC PAY 1,27,185.00 GIS -120
        Name: RAMESH RUPIREDDY STAGNATION PAY 4,405.00 GPF Contribution -8,000.00
        Desig ADE Stagnation Pay GPF 17,620.00 DA Cr to GPF 4,370.00
        Seat No: CPRS-S2-1 D.A. 37,685.00 LIC -5,074.00
        BANK: SBHY0020138 D A(GPF) Arr 4,726.00 Professional Tax -200
        A/c No 52135288942 H.R.A 15,000.00 INCOME TAX -50,000.00
        Net Pay: 135743 GENERATION ALLOWAN 24,637.00 SFMS -500
        PPO No COAL HANDLING ALLO 455 bank loan -36,650.00
        CONVEYANCE ALLOWAN 1,500.00 society -5,170.00
        Shift Allw 6,822.00
        Sl.No 2 Leave encashment A 2 Total Deductions -1,10,084.00
        CUG ALLOWANCE 83
        PLF ALLOWANCE 2,767.00
        TELANGANA INCREMEN 955
        24X7 POWER INCREME 1,985.00

        Earnings 25,115.00
        Earnings 2,20,712.00

        Total Earnings 2,45,827.00
        ************ ***************************** ****************** ********************************** ********************** *****************
        Personnel#: 1010410 BASIC PAY 1,31,590.00 GIS -360
        Name: SANTOSH PASPULATTI Basic GPF Arr 3,46,520.00 GPF Contribution -7,895.00
        Desig ADE D.A. 37,685.00 DA Cr to GPF 17,428.00
        Seat No: CPRS-S2-1 D A(GPF) Arr 96,198.00 LIC -27,822.00
        BANK: SBHY0020138 H.R.A 15,000.00 Professional Tax -600
        A/c No 52135320498 HRA Arr 43,500.00 INCOME TAX -1,96,300.00
        Net Pay: 474143 GENERATION ALLOWAN 24,637.00 SFMS -1,500.00
        PPO No Gen.Allw Arr 6,358.00
        COAL HANDLING ALLO 455 Total Deductions -2,51,905.00
        Coal Handling Allo 117
        Sl.No 3 CONVEYANCE ALLOWAN 1,500.00
        Conveyance Allw Ar 387
        Shift Allw 6,822.00
        Shift Allw Arr 1,761.00
        CUG ALLOWANCE 83
        CUG Allowance Arr 83
        PLF ALLOWANCE 1,598.00
        TELANGANA INCREMEN 955
        TELANGANA INCREMEN 2,515.00
        24X7 POWER INCREME 2,280.00
        24X7 POWER INCREME 6,004.00

        Earnings 5,05,041.00
        Earnings 2,21,007.00

        Total Earnings 7,26,048.00
        ************ ***************************** ****************** ********************************** ********************** *****************
        Personnel#: 1010411 BASIC PAY 1,35,995.00 GIS -120
        Name: RAMESH BANDI F.P.I 75 GPF Contribution -20,000.00
        Desig ADE D.A. 38,946.00 LIC -6,125.00
        Seat No: CPRS-S2-1 GENERATION ALLOWAN 24,637.00 Professional Tax -200
        BANK: SBHY0020138 Gen.Allw Arr -367 INCOME TAX -37,100.00
        A/c No 52135294116 COAL HANDLING ALLO 455 SFMS -500
        Net Pay: 138147 Shift Allw Arr -1,320.00
        PPO No PLF ALLOWANCE 536 Total Deductions -64,045.00
        TELANGANA INCREMEN 955
        24X7 POWER INCREME 2,280.00
        Sl.No 4
        Earnings -1,151.00
        Earnings 2,03,343.00

        Total Earnings 2,02,192.00
        ************ ***************************** ****************** ********************************** ********************** *****************
        Personnel#: 1035357 BASIC PAY 2,48,330.00 GIS -120
        Name: SURESH KUMAR ERIKI STAGNATION PAY 10,560.00 GPF Contribution -15,533.00
        Desig ADE F.P.I 860 LIC -3,154.00
        Seat No: CPRS-S2-1 D.A. 74,141.00 Professional Tax -200
        BANK: SBIN0021031 H.R.A 15,000.00 INCOME TAX -99,600.00
        A/c No 52108831007 GENERATION ALLOWAN 24,637.00 SFMS -500
        Net Pay: 270998 DUST ALLOWANCE 275
        PPO No CONVEYANCE ALLOWAN 1,500.00 Total Deductions -1,19,107.00
        Shift Allw 6,822.00
        CUG ALLOWANCE 83
        Sl.No 5 PLF ALLOWANCE 2,767.00
        TELANGANA INCREMEN 1,750.00
        24X7 POWER INCREME 3,380.00

        Earnings 2,767.00
        Earnings 3,87,338.00

        Total Earnings 3,90,105.00

        what I want is I want get all these into a table format

        like
        Sno Personnel#: BASIC PAY STAGNATION PAY F.P.I D.A. GENERATION ALLOWAN COAL HANDLING ALLO CONVEYANCE ALLOWAN Shift Allw CUG ALLOWANCE PLF ALLOWANCE TELANGANA INCREMEN 24X7 POWER INCREME Total Earnings GSLISA GIS GPF Contribution Professional Tax INCOME TAX CC CHARGES CC CHARGES SFMS Total Deductions

        the raw data is consist of 100's of employees
        I want the data transposed to table format with above headers and corresponding values, each employee data is separated with ******* row and for each emp have the earning in one column and deduction in another column

        really appreciated your help

        thank you in advance.

        1. is there a way to share the sample file let me know so it will be much easier to understand

  18. I want formula for two sheet vlookup then i want c column value against b& a

    1. ? I have sheet for real estate customer payment for rent each customer with deferent code number have how can i make the formula for each code that remind me to pay then the status for each customer after unpaid show paid on that date.

      1. Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.

      2. please support me

  19. WELL STRING TEST DATE BLPD
    A 01/01/2015 1,246
    B 05/10/2018 879
    C 07/03/2015 1,135
    D 12/09/2015 674
    A 06/10/2015 978
    B 28/12/2015 0
    C 20/01/2015 1,498
    D 02/07/2015 957
    A 10/08/2015 1,189
    B 15/11/2015 654
    C 21/05/2015 1,359
    D 31/01/2015 31
    .....Continue

    This is the data i have. In this data the well string name is going to repeat multiple times with different test date & BLPD value.

    Now i have another set of data as follows:

    WELL STRING ACTIVITY DATE TEST DATE NEAREST TO ACTIVITY DATE BLPD
    A 04/02/2015 ? ?
    B 07/11/2018 ? ?
    C 12/04/2015 ? ?
    D 13/08/2015 ? ?
    A 05/12/2015 ? ?
    B 26/11/2015 ? ?
    C 10/03/2015 ? ?
    D 04/08/2015 ? ?
    A 12/07/2015 ? ?
    B 14/10/2015 ? ?
    C 25/06/2015 ? ?
    D 31/07/2015 ? ?

    How to enter the formula for question mark?

    1. Sorry, I do not fully understand the task. To understand what you want, give examples of the desired result.

      1. For example: In 1st row of second data base... Well String A has activity date 04/02/2015. In row 1 Column C3 of database 2, we want "TEST DATE NEAREST TO ACTIVITY DATE". Now we go to data base 1 & search the test date nearest to activity date 4/02/2015 for Well String A. In database 1, we can see there are 3 test dates against well string A & the test date nearest to activity date(04/02/2015) is 01/01/2015. So in database 2: R1C3 will be 01/01/2015 & R1C4( BLPD: Barrel Liquid per day) will be 1,246 as given in database1. I want Column 3 & Column 4 of database 2 to be derived from database 1 with the help of formula. Please suggest.

        1. Hello! Use the FILTER function to get values for Well String A only. Then use INDEX MATCH function to find the BLPD value for the nearest date.

          =INDEX(FILTER('1'!A1:C12,'1'!A1:A12='2'!A1), MATCH(MIN(ABS('2'!B1-FILTER('1'!B1:B12,'1'!A1:A12='2'!A1))), ABS('2'!B1-FILTER('1'!B1:B12,'1'!A1:A12='2'!A1)),0),3)

          Hope this is what you need.

  20. I have 4 columns in sheet1 (Job No, Operation Code, Budget, Actual) and I have same 4 columns in sheet2 too(Job No, Operation Code, Budget, Actual).
    I want to fill columns Budget and Actual of sheet2 automatically when the Job No and Operation Code matches in both the sheets. I tries using Index, Match and Vlookup but all are giving me either incorrect values or no match even though exact match is present. Giving here sample data. Please, can anyone help me in this case? I am really trying to find solution and learn new tricks in excel. Thankyou in advance.

    Sample data of Sheet1:
    Job No Operation Code Budget Actual
    Job01 Design 0 5.27
    Job02 CNC 0 30.97
    Job03 Design 0 9.92
    Job04 CNC 0 23.35
    Job05 EDM 0 4.78
    Job06 Grinding 0 30.32
    Job07 ManualLabor 0 1.03
    Job08 Design 0 281.27
    Job09 ManualMachining 0 24.52
    Job10 CNC 0 1287.17
    Job11 EDM 0 102.88

    Data in Sheet2:
    Job No Operation Code Budget Actual
    Job01 Design ? ?
    Job02 CNC ? ?
    Job03 Design ? ?
    Job04 CNC ? ?
    Job05 EDM ? ?
    Job06 Grinding ? ?
    Job07 ManualLabor ? ?
    Job08 Design ? ?
    Job09 ManualMachining ? ?
    Job10 CNC ? ?
    Job11 EDM ? ?

    I need to fill in cells where ? is present with correct matching values from sheet1.

    What I have tried:
    1) For "Budget" column match:
    =IFERROR(INDEX(C$2:C$12,MATCH($A16&$B16,$A$2:$A$12&$B$2:$B$12,0)),"no match")

    For "Actual" column match:
    =IFERROR(INDEX(D$2:D$12,MATCH($A16&$B16,$A$2:$A$12&$B$2:$B$12,0)),"no match")

    These gives me "no match" even thought there is a match
    2) For Budget column match:
    =VLOOKUP($A16&$B16,$A$2:$C$12,3,FALSE) ----> Gives #N/A, even though there is a match.

    For "Actual" column match:
    =VLOOKUP($A16&$B16,$A$2:$B$12&$D$2:$D$12,3,FALSE) ------------------> Gives #Value, even though there is a match.

      1. Hey Alexander. I tried again with the formulas present on the page Excel INDEX MATCH with multiple criteria - formula examples", which you shared with me in your above comment.
        It worked absolutely fine and my sheet is fetching data as expected!
        Thankyou so much for your help and time !!!!

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

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

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

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

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

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

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

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

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

  30. 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","")

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  46. 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),"")

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

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

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

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

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