Comments on: How to VLOOKUP across multiple sheets in Excel with examples

When looking up some information in Excel, it's a rare case when all the data is on the same sheet. More often, you will have to search across multiple sheets or even different workbooks. The good news is that Microsoft Excel provides more than one way to do this. Continue reading →

Comments page 2. Total comments: 136

  1. Hi all,

    It is my very first time sharing a mind pickle I have with Excel, so please bare with me.

    First of all I would like to thank for the great tutorial that is displayed! Very helpful!

    So here is my pickle, so to speak:

    Initially I have 20+ sheets containing different prices and different products, all of the data is structured the same on every sheet, e.g column A contains date, B - product name, C - price.
    In my main sheet I have a table which I want to be able to have a drop down dependent list, which I have created.
    E.g: If you select supplier X, you get the products that he is selling in the next cell.

    Here is the tricky part (I used nested IFs, but as you may know the formula became almost the lenght from here to the moon):
    When I select supplier X and it shoes the product, I want in another cell to have the price of the chosen product, based on the selected supplier.
    Now i got arround this by using nested IFs with VLOOKUP, but the formula is too long and if I need to add a new sheet, I have to rewrite formula and namings and its a bit frustrating.
    I tried using the INDIRECT, but some how I get a REF...

    In biref I want to be able to select from a drop down a supplier on column A, on column B to be able to select the chosen suppliers's products, and on column C to have automatically filled the selected product price.

    Do you have any advise on this?

    Please let me know if you need any more info.

    Thank you!

    1. Dear Romanov (AsSalaam-u-Alaiykum - Peace be upon you)

      You can use two or more drop down lists dependent on each other. I have used it somewhere but I forgot it now. But you can google it as I had done it through the same trick.

  2. Thanks for this helpful article - I used the method with the below formula across multiple worksheets:

    =VLOOKUP(X2, INDIRECT("'"&INDEX(Lookup_Sheets, MATCH(1, --(COUNTIF(INDIRECT("'" & Lookup_Sheets & "'!R$4:R$27"), X2)>0), 0)) & "'!R$4:AJ$27"), 16, FALSE)

    The problem i am having is that where X2 (which is a comment/string of text) is long, the formula returns the error "VALUE"
    When i reduce the characters in X2, it returns the correct lookup entry.
    I have also tried changing to TRUE and also tried adding X2&"*" but none of these work either.

    For further context, X2 is a sheet of data pull from a software system, each row is allocated to a specific team (separate sheets) where X2 is replicated in R4:R27. On each team sheet, a name is filled out in a colum to the right of this data. I need those people's names to "match" and populate on the original data pull sheet for summary. Not sure if the error lies within the original data sheet or the separate sheets they are being duplicated on.

    Is there any way around this please?

  3. Hi,

    Thank you for providing these helpful tips.

    I would like to know if there is a way to lookup a maximum value across multiple sheets? We have data updated every month and trying to set up a consolidated spreadsheet pulling information from monthly updated spreadsheets. Is it possible. Very much appreciated if you could please share your ideas.

    Thanks.

    1. Hello!
      To work with updatable data across multiple sheets, I recommend using a Pivot Table. You can set up a pivot table field to get the maximum value.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  4. Hi, how can I vlook up in 12 sheets it always says entered top many arguments

  5. HI

    Under your title Vlookup multiple sheets with INDIRECT

    you havent defined what is "Sheet_list"

    Kind Regards
    Maurice

    1. Hi Maurice,

      My bad. Actually, there is no "Sheet_list" in the formula. It's just another name for the Lookup_sheets named range. Initially, I named it "Sheet_list", then changed the name to "Lookup_sheets", but failed to replace one instance in the generic formula. Sorry for that.

      Anyway, the formula is fixed now. Thank you for pointing our that mistake!

  6. Hi Milosz

    Have two spreadsheets with multiple rows. Each row has a unique ID, pay component code and amount. I am try to do a v-lookup between the spreads to check the the amounts against each pay component match.

    Please can you tell if any of the above would work in this scenario or do I need todo something completely different?

    Thanks
    Becky

  7. Hi,
    I am trying to use the formula under 'Lookup multiple sheets between workbooks'
    The spreadsheet I am using will be opened by multiple colleagues across the business but the workbook it is linking to will not be open. Therefore using the COUNTIF function is returning #N/A:

    =(VLOOKUP($AH8, INDIRECT("'[Book1.xlsx]"&INDEX(Sheet_list, MATCH(1, --(COUNTIF(INDIRECT("'[Book1.xlsx]"&Sheet_list&"'!$B$17:$B$32"), $AH8)>0), 0))&"'!$B$17:$K$32"), 4, FALSE))

    Is there a way to fix this so that it works when Book1 is not open?

    Thanks

  8. Hi, great tutorial on Vlookup. First time user of Vlookup. My first formula worked fine; =VLOOKUP(D4,CREWS2!A1:E55,2). The second formula; =VLOOKUP(C8,CREWS2!$A$1:$E$55,1)is producing an odd result. Both formulas use the same data table;
    A B C D
    GRF Groundperson 6 Months - ST $60.72 ST
    GRG Groundperson 6 Months - PT $88.05 PT
    JTA Journeyman Tree Trimmer - ST $95.68 ST
    JTB Journeyman Tree Trimmer - PT $138.74 PT
    the first formula finds column A when the name in column B is entered. my second formula is to find column B when the name in column A is entered. When i run second formula I am getting GRG when i enter Journeyman Tree Trimmer - ST, instead of JTA.
    Does the direction column B to column A matter? Does the name structure matter; Journeyman Tree Trimmer - ST?
    Thank you for any assistance

    1. Hello!
      The VLOOKUP function always searches in the leftmost column of the table and returns a value from a column to the right. To get the value to the left of the lookup column, use the INDEX + MATCH combination.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  9. Trying to make the vlookup indirect function work for my data and can't quite get my head around it.

    What I have based on your examples above is:

    =IFNA(VLOOKUP($B2, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(1, --(COUNTIF(INDIRECT("'" & Lookup_sheets & "'!$C$2:$C$20"), $B2)>0), 0)) & "'!$A$2:$R$20"), 18, FALSE), "Not found")

    B2 = lookup value
    C2:C20 across the multiple sheets is range the lookup value will be in
    A2:R20 is my table/data
    Column 18 or R is where i want to return the value from that matches the C2 result.

    The only thing being returned no matter what i enter in B2 is "Not found""/NA. Any help is much appreciated.

    1. Hello!
      I cannot verify your formula as I do not have your data. Use the instructions carefully. Check the named range Lookup_sheets. In it, you need to write down the names of the worksheets.

    2. I'm trying to search for a customer number (B2) that returns text/notes from column R/18 across multiple worksheets and the text is not the same across each occurrence. Is this possible?

  10. Multiple sheets linked to one sheet, sample if i have 4 sheets of my inventories and i want to monitor all damages in fifth sheets.

  11. Fiz uma formula com Vlookup para buscar o ultimo valor pago de determinado item da planilha 01 para a planilha 02. (=VLOOKUP(A2, Sheet3!E:I, 5, FALSE), porém o retorno que aparece é só do primeiro valor pago.
    Como fazer para aparecer o último valor pago?

  12. Hi

    I need to use VLOOKUP function for searching a value in 250 sheets of a different workbook. What formula better to use?

    Thank you

  13. Hi, I have been working on a formula for days and I need help. Sheet 1 Cell E2, I need a return value from based on what is in Sheet 2 Cell I2

    IN E2, I would like the value "SWM00009" if Sheet 3 cell I2 equals one of 2063, 2076, 2088, 2098, 2109, 2118, 2121, 2125, 2127, 2132, 2135, 2148, 2152

    "SWM00002" if Sheet 3 cell I2 equals one of 2010, 2039, 2053, 2150, 2154, 2591, 2629

    "SWM00004" if Sheet 3 Cell I2 equals one of 2668, 2687, 2840, 2878, 2903, 2989, 3001,3002

    I need multiple conditions and return values. HELP! Thanks in advance.

    1. Hello!
      Use nested IF statements:

      =IF(SUM(--(I2={2063,2076,2088,2098,2109,2118,2121,2125,2127,2132,2135,2148,2152}))>0,"SWM00009",IF(SUM(--(I2={2010,2039,2053,2150,2154,2591,2629}))>0,"SWM00002", IF(SUM(--(I2={2668,2687,2840,2878,2903,2989,3001,3002}))>0,"SWM00004","")))

      This should solve your task.

  14. Hi there,

    I desperately need help with a formula. This site is excellent I just can't make the formula work for my situation, I'm due to go on maternity leave in a few weeks and need to sort out the following formula for my replacement.

    In worksheet 1 - I have employee numbers which I am able to use VLOOKUP to match to ascertain if the employee is listed in worksheet 2 long term sick tracker.

    FORMULA USED=IF(VLOOKUP(C3,'3.Long term sick Tracker'!$A$1:$A$10044,1,FALSE)=0,"No","Yes")

    The problem is I need to enter a new criteria to detect in worksheet 2 long term sick tracker- if the employee is active - column M.

    Can anyone help me. I've never asked for excel help before, but desperately need it.

    Thanks

    Nicky

  15. Hello!

    I am using the below formula in Google sheets, I noticed it functions in Excel but does not return the values in Google Sheets. Is there a different way this needs to be formatted to operate in google sheets? I noticed in one of the comments Import range is mentioned as a fix for google sheets. Is that what would need to be edited? if so where in the formula would this need to be added?

    =ArrayFormula(VLOOKUP($A2, INDIRECT("'"&INDEX(LookupSheets, MATCH(1, --(COUNTIF(INDIRECT("'"& LookupSheets&"'!$A$2:$A$30"), $A2)>0), 0)) &"'!$A$2:$L$30"), 12, FALSE))

  16. Hi,
    I used the IFERROR nested function but It says I used the maximum amount of IFERROR in one formula, but I still had more sheets in a workbook. What should I do?

  17. How do I use vlookup from 4 different tabs to pull data into Master tab in the same excel

  18. Dear. I am using this formula, it work for Excel with multiple sheet, however, it got some error in google sheet

    =ARRAY_CONSTRAIN(ARRAYFORMULA(ROUNDUP(VLOOKUP($E11, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(1, --(COUNTIF(INDIRECT("'"& Lookup_sheets&"'!$A$2:$A$1000"), $E11)>0), 0)) &"'!$A$2:$P$1000"), 12, FALSE),3)), 1, 1)

    The lookup_sheet (named ranged)
    1. Kitchen
    2. Show Kitchen
    3. Bakery

    from three above, excel work it and able to find the value. However, when perform in google sheet, the indirect unable to look for the value, the lookup value only able to find the first row of named ranged which is KITCHEN. May i know any solution on this?

    1. Hello Reeve,

      To get help with your formula, consider sharing an editable copy of your Google spreadsheet (not Excel) with us (support@apps4gs.com). To grant us access to your data, press the Share button at the upper right corner of your Google Sheets and enter support@apps4gs.com. Let us know where the formula is and we'll look into it.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

  19. I am trying to combine a double Vlookup function with and IMPORTATRANGE function. Basically:

    1)
    I want to import earnings data from this sheet: https://docs.google.com/spreadsheets/d/1Le-ZyVh_2iS2ZqaCq9y5sCLPrH5aIMjx9fLtTWMK1lw/edit#gid=0

    To this other sheet: https://docs.google.com/spreadsheets/d/1ZD5yfybdogWJ0Bmb-7J9V_VJtkEoLPV8gPZwHwWr7es/edit#gid=0

    To import datat I used the following formula: =Vlookup(B4, IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Le-ZyVh_2iS2ZqaCq9y5sCLPrH5aIMjx9fLtTWMK1lw/edit#gid=0","Earnings!A1:D"),2,0)

    2) This is where I get stuck...
    I want to do a second vlookup that will in a way filter the data according to the view of the Month that I select in Cell B2 (January, March, or April). I don't want to add more columns, I just want columns B and C to display the data.

    I figured using a double vlookup + an Importrange function would work, not sure if there is an easier way. Can you please help me?

    1. Hello Jose,

      I replaced the third argument of your VLOOKUP with the MATCH function to find the number of the column required based on B2 contents:
      =Vlookup(B4,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Le-ZyVh_2iS2ZqaCq9y5sCLPrH5aIMjx9fLtTWMK1lw/edit#gid=0","Earnings!A3:D"),MATCH($B$2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Le-ZyVh_2iS2ZqaCq9y5sCLPrH5aIMjx9fLtTWMK1lw/edit#gid=0","Earnings!A2:D2"),0),0)

      The formula is also in your spreadsheet :)

  20. I have an Excel file with ~250 worksheets; each worksheet has a unique name (the first and last name of an employee). We asked our employees a series of 10 questions. On every sheet, column C is a "yes" column; column D is a "no" column. If they answered "yes", we entered a 1 into Column C on their worksheet; if "no" we entered a 1 in column D. I am trying to determine how to summarize the total number of employees that answered yes (Column C) or no (Column D) from all 250 work worksheets. Is there a way for me to query/lookup/reference the data without manually linking each of the 250 worksheets (shift, select cell C9 (yes), or shift, select cell D9 (no) for all 250 worksheets?

    1. Hello!
      Here is the article that may be helpful to you: 3-D reference in Excel: reference the same cell or range in multiple worksheets
      If you want to sum the same cell from all sheets except the current one, just enter a formula like this:

      =SUM('*'!C9)

      The sign * serves as a wildcard, which is interpreted as “all sheets except this one”. When you press Enter after entering this formula, Excel converts the formula to use the actual sheet names.
      We have a ready-made solution for your task. You can consolidate data from multiple worksheets with Consolidate Sheets. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

  21. Hi,

    I am looking to drag a vlookup formula from column C to to column CK with =VLOOKUP($A$4,NEW!$A:$CJ,3,0) but I cant get the col_index_num to change as I drag (I want it to go from 3 to 4 to 5 etc as I drag along from left to right)

    thanks

    1. Hello!
      If your formula was written in column C, try using the COLUMN() function instead of 3 :

      =VLOOKUP($A$4,NEW!$A:$CJ,COLUMN(),0)

      I hope it’ll be helpful.

  22. Hello Alexander,

    Very nice and useful article. It really helped me to reduce my length of my formula.
    I applied formula to lookup values from multiple excel sheet, however considering total records and array size indirect function is taking too much time to refresh.
    Can you please advise any non-volatile function / any other alternate way to improve file performance.

    Below is my formula for reference -
    {=VLOOKUP($A2, INDIRECT("'"&INDEX(Lookup_Sheets, MATCH(1, --(COUNTIF(INDIRECT("'"& Lookup_Sheets &"'!$A:$BA"), $A2)>0), 0)) &"'!$A:$BA"), MATCH(B$1, Column_List, 0), FALSE)}

    Lookup_Sheets : is defined name for list of my excel worksheets
    Column_List : is defined name for column header, column header are same in all worksheets

  23. Hi,

    I need to returns a value based on the sheets the matching data is found:
    On the delete sheet = Delete
    On de diregard sheet = Disregard
    On the acton sheet = Acton
    ETC. up to +/- 10 sheets

    If no data is found in any sheet, the value "keep" should be set.

    I started with this, (I fished somewhere on the net), it works to compare with one sheet, I need it to continue to lookup the other sheets:
    =IF(ISERROR(VLOOKUP(J4;DeleteList!$A$1:$A$34974;1;FALSE));"Keep";"Delete")

    After reading your article, I got this to work, but it does not give the expected result, I don't need it to return the value found, or to tell me it did not find the value + to continue to the next sheets:
    =IFERROR(VLOOKUP(J4;DeleteList!$A$1:$A$34974; 1;FALSE); IFERROR(VLOOKUP(J4;Disregard_HW!$A$1:$A$34974;1;FALSE); "Not found"))

    I plan the use that as a filter afterward with a pivot table.

    I hope I was a bit understandable.

    Thank you
    Br,

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(ISNUMBER(MATCH(J4, DeleteList!$A$1:$A$34974, 0)),"Delete",IF(ISNUMBER(MATCH(J4, Disregard_HW!$A$1:$A$34974, 0)),"Disregard","Not Found"))

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

      I'd recommend you to have a look at our Ablebits Data - Compare multiple sheets tool that can help you. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
      I hope it’ll be helpful.

      1. Thank you Alexandre,

        This is works!

        On last question, this would be a must:
        The rawdata is an import and in the concerned cell (J column), for some reason an apostrophe is set in front " 'data ";
        But I copy towards the concerned sheets from out the pivot table;
        Because of that your formula was not working at first as it is looking for an exact match:
        " data " not equal to " 'data " = not found.

        Is there a way around this?
        The data are unique numbers (8 numbers 12345678), if this helps

        I have no admin right to install your trial "Ablebits Data", but I look it up on my personal computer, and submit it to our IT departement if I find a use for it.

        Thanks again.

        1. Hello!
          This problem often appears when importing data from another program. It is necessary to convert numbers written as text into numbers. To do this, use the guidelines from this article or the Convert Text tool from the Ultimate Suite for Excel.
          If something is still unclear, please feel free to ask.

  24. I used the formula above to pull data from one spreadsheet to another but it's not working. It just shows my formula in the cell. However, in the "Function Arguments" box it shows the information for the first person on the sheet I'm trying to pull the data from. Can you tell me what I'm doing wrong?

    1. Hi Kevin,

      When a cell displays a formula instead of the calculated value, most likely it's because of one of the following reasons:
      - Show Formulas mode is turned on, or
      - A formula is entered as text

      For more information, please see How to resolve Excel formulas not calculating.

  25. I am using Google Sheets I want to change the following formula so that it can search data from another worksheet:
    =IFERROR(INDEX($AL$2:AL,MATCH(1,($AJ$2:AJ=B6)*($AK$2:AK=D6),0)))

    This works if I maintain all of my data in the same sheet but my Zapier automation requires me to transfer columns AJ,AK,and AL from the previous formula to another worksheet. There AJ=A, AK=B, AL=C. I tried using the following formula because I don't want to import data into my current worksheet other than that which is required:
    =IFERROR(INDEX($AL$2:AL,MATCH(1,((VLOOKUP(B5,'Info de otras hojas'! $A$2:A,1,FALSE*(VLOOKUP(D5,'Info de otras hojas'! $B$2:B,3,FALSE))),0))))).
    Also, I'm not sure about how to include the first part after INDEX in VLOOKUP.

    HERE IS THE LINK TO MY GOOGLE SHEET:
    https://docs.google.com/spreadsheets/d/1ZMaMtk9kDNqX8SckJ8P90YLjixTpGS4PDX9kCt5eyUM/edit?usp=sharing
    Could you please help me?

  26. Hello,
    please trying to use the Vlookup function in a excel data sheet but having some challenges.
    the challenge is that; after writing the logic for the function in a cell and running it down to cover the data, some cell return with #N/A or different corresponding infomation but when i copy the figure am trying to vlookup for in the data i find the figure in the data.

    any help?

  27. Hi! Thank you for your explanaiton. I've modified the formula to reference tables instead of sheets, and use xlookup instead of vlookup, and it looks like this:
    =XLOOKUP([@Key],INDIRECT(INDEX(Tablas,MATCH(1,--(COUNTIF(INDIRECT(Tablas&"[Key]"),[@Key])>0),0))&"[Key]"),INDIRECT(INDEX(Tablas,MATCH(1,--(COUNTIF(INDIRECT(Tablas&"[Key]"),[@Key])>0),0))&"[Estatus en Nomina]"),"")

    (tablas=lookup_sheets in your example)

    However, when I run the formula I get this error:
    COUNTIF({#value!;#value!;#value!;#value!;#value!},[@Key])
    It should give me 0 for all of them, but it turns into
    --({0;0;0;1;0}>0

    thus giving me the wrong value in the end. How can I fix this?
    Hope this was clear enough. Thanks again!

    1. NVM found my mistake :) thank you!

      1. Hi Alejandra, are you able to share your solution to this? I really need to use XLOOKUP and cannot figure out how to change the original formula.

  28. Hi. I'd like to start by commending you on your tutorials on this site. They've been extremely helpful and have become invaluable.

    However, although I have found a tutorial on looking up multiple matches based on mutliple conditions this only shows how to do this within the same worksheet. Also whilst the tutorial above shows how to look up values accross worksheets and workbooks I can't see it shows how to get multiple matches.

    So is it possible to lookup and return multiple matches accross multiple worksheets and if so how?

    Many thanks for your help.

    1. Hello!
      Please have a look at this article: How to find duplicates in Excel
      In the COUNTIF function, use links to other sheets in the workbook.
      I'd recommend you to have a look at our Ablebits Data - Compare tables tool that can help you to find duplicates in two tables.
      It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free

      I hope my advice will help you solve your task.

      1. Thank you so much Alexander for your reply, however, maybe I didn't explain what I'm trying to do clearly enough.

        I want to lookup values which appear more than once in a table and retun data from a corresponding column just like this

        https://www.ablebits.com/office-addins-blog/vlookup-multiple-values-excel/

        Except I want to lookup more than 1 value each of which appear more then once in each table and these tables are in different sheets and then return data from a corresponding column in the same tables. Exactly as described in the link above but the data to be looked up is in different worksheets to that where the formula is to be entered.

        Please can you help?

        Many thanks.

        1. Have you ever gotten an answer for this?

  29. Hello.. Alexander,

    I have a problem regarding vlookup and then sum for values which are came by vlookup. as example there is data in cell A1,C1 and E1.
    By using vlookup we call values from other sheet to B1,D1 and F1 respective to A1, C1 and E1. Now we take sum of B1,D1 and F1 to cell G1.

    Now problem is that if there is any empty cell in column A,C or in E the value will not came in respective cell by vlookup and the sum function shows #value error in column G.

    A. B. C. D. E. F. G.
    1. 001. 2. 002. 2. 003. 4. 8.
    2. 004. 2. 005. 3. 006. 1. 6.
    3. 007. 1. 008. 3. EMPTY N/A. #VALUE

    well, im working on old data sheet and there is 8 columns like this and some other data))..
    I tried some formulas but not getting result. Is there any function that ignore empty cell and calculate only that cell which contains value from vlookup.

    1. In short, is there any other options for this?

      =IF(V1="",IF(S1="",IF(P1="",IF(M1="",IF(J1="",IF(G1="",IF(D1="",IF(A1="","",A1),+A1+D1),+A1+D1+G1),A1+D1+G1+J1),+A1+D1+G1+J1+M1),+A1+D1+G1+J1+M1+P1),+A1+D1+G1+J1+M1+P1+S1),+A1+D1+G1+J1+M1+P1+S1+V1)

    2. Hello!
      Enclose the VLOOKUP function in the IFERROR function to return 0 or "" instead of an error.
      You can learn more about IFERROR with VLOOKUP in Excel in this article on our blog.

  30. I have a workbook showing an employee list and a breakdown of their hours worked for each week. A tab will be added each week; this workbook also has a summary sheet, to compile the info from all tabs. I am currently using a VLOOKUP formula, but it has to modified each time a tab is added. This is being done across 15 columns, so updating the formula is a bit cumbersome. Is there a better way to accomplish this? Could the VLOOKUP look at a range of sheets (the layout is the same for each sheet), with new tabs being inserted within that range to avoid having to update the formula each week?

    Example: Employee names are listed in column A, but may not be in the same row, in every sheet; the breakdown of time is across columns C - N (ie, Training, Holiday, Jury Duty, Regular). This layout is the same in each sheet, including the Summary.

    My formula to summarize Holiday hours for the first employee on the list looks like this:
    =VLOOKUP($A10,'TOTAL HRS SHEET 01-02'!$A:$J,7,FALSE)+VLOOKUP($A10,'TOTAL HRS SHEET 01-09'!$A:$J,7,FALSE)+VLOOKUP($A10,'TOTAL HRS SHEET 01-16'!$A:$J,7,FALSE)+VLOOKUP($A10,'TOTAL HRS SHEET 01-23'!$A:$J,7,FALSE)+VLOOKUP($A10,'TOTAL HRS SHEET 01-30'!$A:$J,7,FALSE)+VLOOKUP($A10,'TOTAL HRS SHEET 01-31'!$A:$J,7,FALSE).

    This is just January. It works, but is there a better way?

  31. I am new to excel. Dealing purely with numbers and some letters. I have multiple worksheets with data in the same workbook maybe 15 to 30 worksheets. What I want appears simple to me. I need to know how many cells have the same content wanting excel to return the exact number of cells and where they are. For example a cell might have E3 F12 G10 in it and appear 20 times in different locations say worksheet called "Table 5" and another called Table 17" etc or if in the same worksheet but different column like cell "B55230" and the other cell "F20456" in column F. Please let me know if the countif with vlookup functions can help.

  32. Hi! Thank you for this! I am not sure if this is the right place for this. I am trying to find a specific formula. My workbook has several worksheets of data. Each worksheet represents a year worth of data. On the first page we are analyzing the overall data and showing the overall average, as well as which years have the max and min of certain stats. I have figured that out. But I want to figure out how to display on the first sheet, which sheet that value came from. One of the tricks is that we are not comparing the same cell in each sheet because we have a different number of transactions from year to year.

    For example, this is on of the Max formulas:
    =MAX('2020'!B61,'2019'!B46,'2018'!B45,'2017'!B41,'2016'!B37,'2015'!B39,'2014'!B35,'2013'!B41)

    1. Hello!
      I recommend creating a separate table with totals from each sheet. In this table it will be easy to find the maximum value and determine the year.

  33. I have a given formula that pulls the scores from another tabsheet. Summary tabsheet and Assessment tabsheet.

    On Summary tabsheet, I have this given formula.
    =IFERROR((VLOOKUP($Y14,'Pre-SAx Assessment'!$E$2:$J$8225,6,FALSE)),"0")

    where Y14 is the value used for search

    There are multiple repeated scores from the "Assessment" tabsheet in column J. Without sorting the Assessment tabsheet table, it just return the first value it found. How can I improve on this given formula using the 'max' option within?

    1. Hello!
      If I understand your problem correctly, you want to find the last match. This is not possible with VLOOKUP. I recommend using the LOOKUP function and these examples.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  34. I have implemented this into a Google Sheets document that I am working on. However, it doesn't return any values from any sheets but the first listed in the named range. This could be a Google sheets issue, or I'm missing something...

      1. Thanks for your reply, Natalia. I compared the reference you provided to my formula, and I don't see a difference in the syntax. If you have a moment, I'd love to allow you to take a look at my sheet and see if you can discern the issue. I've exhausted different tweaks in the formula, as well as the datasets. No matter what, I can't get the calculations to advance to the next sheet in the named range.

  35. This is working good for me vlookup value for multiple sheets
    =VLOOKUP($A2, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(1, --(COUNTIF(INDIRECT("'"& Lookup_sheets&"'!$A$2:$A$6"), $A2)>0), 0)) &"'!$A$2:$C$6"), 2, FALSE)
    at the same time i want cell reference " i want cell address instead of value)
    =CELL("address",VLOOKUP($A2, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(1, --(COUNTIF(INDIRECT("'"& Lookup_sheets&"'!$A$2:$A$6"), $A2)>0), 0)) &"'!$A$2:$C$6"), 2, FALSE)) this is not working for me please suggest me actually i want cell address accross multiple sheets.

    1. Hello!
      To return the address of the cell in which the desired value is written, you can use the formula:

      =ADDRESS(MATCH(B2,F1:F21,0),6)

      B2 - the value we are looking for
      F1: F21 - search range
      6 is the column number of the search range (that is, column F)

      1. i know this formula i want multiple worksheets like this =VLOOKUP($A2, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(1, --(COUNTIF(INDIRECT("'"& Lookup_sheets&"'!$A$2:$A$6"), $A2)>0), 0)) &"'!$A$2:$C$6"), 2, FALSE) but i want cell address instead of value

  36. HI,
    You had a lot of great article 1st of all.
    I'm trying to used the Vlookup function with multiple criteria and SUM them.
    Example
    Row1 Product A / Jan 2020 / 8 units
    Row2 Product A / Jan 2020 / 10 Units
    ROw3 Product A / Jan 2020 / 5 units
    The formula should count if the criteria are Product A and Jan 2020 -> 22 Units
    I tried =SUM(VLOOKUP(A2&"-"&B2,A1:C3,2,FALSE)) and it's not working any idea??
    THanks a lot
    Regards

    1. Hello!
      If I understand your task correctly, the following formula should work for you:
      =SUMPRODUCT(--($A$1:$A$7="Product A"),--($B$1:$B$7="Jan 2020"),C1:C7)

  37. hi. my name is janak. thanks for the tutorial, it helps a lot.
    can you explain me the concept of Vlookup multiple sheets with INDIRECT ?
    it contains lots of formulas which make me confuse while evaluating it.
    can you send me the excel sheet with formula & data regarding that concept & evaluation process.

    1. Janak,

      You can find the detailed explanation of the formula in the "How this formula works" section. And here is the sample worksheet with all the formulas discussed in this tutorial.

  38. That is very helpful, bit I am struggling with is how to apply VLOOKUP in only one sheet and return multiple columns if Value I am looking for is in multiple places within row A in that sheet?
    Example: I have list of transactions and values one under the other. In row A I got the name of the project that transaction should be allocated and now I have created new sheet and entering formula:
    =VLOOKUP($A$1, Sheet1!A:A, {2,3,4,5}, FALSE)
    Is there any way for excel to pick up the values in same column and then list them in new sheet made for this Value?

    1. Hi Milosz,

      I do not clearly understand exactly what you are trying to achieve, but your formula won't work anyway - your table_array consists of a single column A while you are requesting to return values from columns 2, 3, 4, and 5.

      Recently, Microsoft has introduced the XLOOKUP function as a more powerful successor of VLOOKUP. Perhaps it could help you fulfill your task.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)