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

  1. Hi, I am having two sheets in 1 sheet I have multiple dates entry with repeated same date as well and I want this multiple dates entry as a single date in another sheet same as pivot table works.

  2. I am using Excel Sheets with Dates and I want to use vlookup() with dates formulas like this VLOOKUP(C7,'02-Jan-23'!$C:$H,6,0)+VLOOKUP(C7,'02-Jan-23'!$C:$K,9,0)-VLOOKUP(C7,'02-Jan-23'!$C:$I,7,0) in next dates sheets to get last dates' closing amounts in new sheets opening and I want to have these sheets names to be changed automatically. Like VLOOKUP(C7,'02-Jan-23-1'!$C:$H,6,0)+VLOOKUP(C7,'02-Jan-23-1'!$C:$K,9,0)-VLOOKUP(C7,'02-Jan-23-1'!$C:$I,7,0).

    Please help me in this situation that how can I get it as in this way I am getting an error.

  3. Hi sir,

    Im working with two table. 1st table contains name, date enter, and date exit. The other table contains date, name, status1, status2.

    My problem is for one person she has multiple of (date enter and date exit) which is from table 2 i need to find between the two dates the text that contains from status 1 and display the corresponding value which is from status 2.

    1. Hi!
      Sorry, I do not fully understand the task. What result do you want to get? Give an example of the source data and the expected result.
      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.

  4. Hi i would like to use vlookup across multiple worksheets to search for value X. All values will be unique and i would like to know if its possible for the vlookup formula to RETURN the sheet name and the location it obtained it from. Eg ( Sheet3!H14) if the data was obtained from that particular cell.

    1. Hi!
      Without seeing your data, it's impossible to give you a formula.
      Use the combination OFFSET and MATCH to find the cell with the desired value. See an example here: OFFSET and VLOOKUP.
      Then apply the CELL function to this cell with the "filename" argument to get the full address of the cell with the file and worksheet names.
      I hope it’ll be helpful.

  5. Hello- I am trying to find if one cell is present on multiple different tabs, each containing a different tab title. Can I conduct a vlookup where I receive an output in a cell with results separated by comma indicating that the tab(s) that cell is found on? Ex: Fruit Apple on maser list, but Apple appears on Tab 1, Tab 3, but not Tab 2. Can I receive an output in a cell on the Master list that says Tab 1, Tab 3?

    Thank you.

  6. How to vlook up for multiple columns and 2 tables. I have to match 3 column values of table 1 with 3 different column values in table 2 and display the column 5 value of table 2 in table 1

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

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

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

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

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

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

  13. Hi,. Thanks for this very good article on Vlookup function. Sir, my problem is that there are four sheets: East, West, north and south having table array: A1:D10, D5:G14, B3:E12 and C4:F13 respectively with four columns of Student I'd, student name, age and father name how we can extract data of student name,age and father name against students I'd at result sheet dynamically with indirect and match function.

    1. Hello!
      Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

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

  15. Hi,

    i need to be able to use VLOOKUP on my current worksheet (sheet1), but compare values in sheet2 to sheet1. I currently use VLOOKUP('SHEET2'!A1, 'SHEET1'!$A$1;$A$100,1,0) but i am getting incorrect values possibly due to 1,0 (index and range). How do i fix the formula?

    I need to stay in sheet1 and cannot use vlookup in sheet2.

    Thank You
    Maksim

    1. Hello!
      The information you provided is not enough to understand your case and give you any advice.
      Explain what values you are getting and what you would like to get.

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

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

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

  19. Como fazer para encontrar o último valor pago da plan 01 e trazer para plan 02
    =VLOOKUP(A2, Sheet3!E:I, 5, FALSE)

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

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

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

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

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

  25. I want use multiple lookup formula
    Date
    Shift
    Lookup value
    Lookup array
    This is data range i want look up lookup value with match with date and shift

  26. HI all
    I need two table array lookups (each in a different worksheet), I've tried this to no avail. I am sure I'm missing something very obvious:

    =VLOOKUP($A1,IF($B1="criteria 1),'[criteria 1 filename.xlsx]list'!$1:$1048576,IF($B1="criteria 2",’[criteria 2 filename.xlsx]list'!$1:$1048576)))), 2, FALSE).

    Many thanks.

  27. HI all
    I need two table array lookups (each in a different worksheet), I've tried this to no avail. I am sure I'm missing something very obvious:

    =VLOOKUP($A1,IF($B1="criteria 1),'[criteria 1 filename.xlsx]list'!$1:$1048576,IF($B1="criteria 2",’[criteria 2 filename.xlsx]list'!$1:$1048576)))), 2, FALSE).

    Many thanks.

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

  29. i need vlookup formua for multiple sheets
    sheet1 - data1(numbers)
    sheet2- data price
    sheet3- that data ids
    sheet4- wana apply all these while taking data1 the remaining sheets also wanna visible on this
    plz if you have this type formulae will you plz share that formula excel on my mail id

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

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

  32. i have a query. i have a sheet with all the roll numbers and students of a class. i have another sheet which have five test marks of students only who have given exams. i have to now copy these marks in in front of respective roll numbers in first sheet. how can i do it. becausew in any second sheet many roll no. s entries are missing as they havent' given exam.

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

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

  35. thank youuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu very much

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

  37. i need to create a excel template that uses vlookups and conditional formatting.
    Backstory: Team coaches need to analyze an employees performance based on a extract pulled daily. this extract is a little confusing and hard to analyze so i want to create a template that will automatically put the data into a table and have conditional formatting based on my needs. im using vlookups to pull the data however when a new sheet is introduced , that formulas become invalid. is there anything for this?

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

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

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

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

  42. I have a working version of a Vlookup across multiple sheets with INDIRECT but I'm trying to include a 'Choose' function so I can specify a custom range to lookup values where the key isn't in the first column E.g. From this:

    =VLOOKUP($B9;INDIRECT("'"&$A9&"'!$A$14:$AR$45");2;FALSE)

    to this:

    =VLOOKUP($B9;INDIRECT("'"&$A9&"'!CHOOSE({1:2},D14:D45,B14:B45)");2;FALSE)

    I can't figure out if a - it's even possible to combine these two and b - if I have a syntax issue given I've changed the value of B9 in line with the value I want to search for in B14:B45 across the other sheets.

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

  44. I have two or three instances of excel open. I needed to do this because my workbooks are very large and they keep crashing (recent issue... anyone else seeing this?). Doing this however, does not allow me to VLOOKUP across instances... Is there a way to do this?

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

  46. Thank you so much Alexander for your reply but I may not have explained what I want to achieve clearly enough.

    I want to do exactly the same thing as described in the following link except the data is in different whorksheets. I want to lookup multiple values which appear multiple time and are in mutliple worksheets and return the data from corresponding columns into another worksheets.

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

    Many thanks

    1. Hello!
      This is a complex solution that cannot be found with a single formula.
      Unfortunately, without seeing your data it is impossible to give you advice.
      I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.

      We'll look into your task and try to help.

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

  48. thanks. very helpful

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

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

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