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 4. Total comments: 192

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

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

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

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

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

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

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

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

  9. thanks. very helpful

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

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

  12. I'm trying to use a portion of a cells contents as a wildcard search to then return the value from a different worksheet that has the matching value of the wildcard search from the lookup_value. Is this possible?

    I tried using VLOOKUP("*"&lookup_value&"*",worksheet array,value needed,FALSE) but it is not finding anything.

    1. Hi,
      The information you provided is not enough to understand your case and give you any advice, sorry. Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result. Please specify what formula you used and what problem or error occurred. It’ll help me understand it better and find a solution for you.

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

  14. I am trying to add lexile values from teenbiz to compare lexile values in NWEA

    1. Hi,
      Sorry, it's not quite clear what you are trying to achieve. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

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

  16. I have used VLOOKUP over 2 sheet previously without any problems.
    Today though, it's telling that I have entered to few arguments for this function. I even used formula tab to make sure that all is filled in.
    =vlookup(D5 '2021Tarrif'!A5:I59 4 0)
    is there something that I'm not doing right?

    1. Hello!
      You wrote down the formula without commas.

      =VLOOKUP(D5, '2021Tarrif'!A5:I59, 4, 0)

      You are using your unique links, I do not have your data. Therefore, I cannot check the work of the formula. If you tell me what the problem is and what the error is, I will try to help.

  17. I'm ridiculously new to excel and with every task I need to look up a formula. My question is: I have two sheets (employees information sheet 1, employee participation sheet 2--it's A LOT of data. I need to find an exact match of an ID on sheet 2 to to sheet 1 and if sheet 1 contains the match, to the right of that match on I need the value to be yes... So what is best?? VLOOKUP or INDEX/MATCH.. I've read everything and I think I've spiraled into an abyss...and when I try what I think is the formula it doesn't work... I'm hopeless.

  18. {=vlookup(A5,sheet1!$B$2:$D$269,3,0)}
    Pls tell me what is the mistake in this formula still the value is not found

  19. Thank You

  20. I am Chandresh,
    Thanks for this information about vlookup.
    I am new for this and try to learn.
    I have problem and I want to help from you.
    I have a data in 'Data' sheet with column's name- date,item no,item,unit and planned, second sheet 'Planned' which has vertical data- item,cum,item no & horizontal data -
    Date(day to day).
    Now I want save my values from 'Data' sheet to 'planned' sheet automatically match to item and day by day.
    Pls help

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

  22. Oh it helped me out soooooooooo much! Thanks a bunch! :) :D

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

  24. Thanks it solve my problem.

  25. Nice article !

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

        1. Hello!
          Without seeing your data, it is impossible to understand your formula. However, I can tell you for sure that the VLOOKUP function can never return you a cell address. It only returns the cell value.

  27. I want to search between different sheets with the vlookup command, so that the name of each sheet is written inside a cell and is dynamic, that is, whenever the cell name is changed to a sheet name, a search is performed inside that sheet.
    Thankful

  28. I am trying to link an insurance rate table (very simple just- age and rate--two columns) sheet with the primary spread sheet with the actual age of each person to determine the rate they will pay. I am using the VLOOKUP as follows =VLOOKUP($I$2,Rate!$A$1:$B$100,2,FALSE) How can I link the actual age of the person on the primary sheet to secondary sheet with the table so the rate will show on the primary sheet?

    1. Hello Cynthia!
      I’m sorry but your task is not entirely clear to me.
      Your formula is spelled correctly. But how it works with your data, I do not know. Explain in more detail what kind of problem arises. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. In that case I will try to help you.

  29. Hi,
    I am trying to use VLook up or Xlookup to connect two work sheets and having issues. I want the worksheet to find an ingredient and link the ingredient from one column to the measurement in another column. SO worksheet 3:

    Xlookup("CHIA SEEDS", worksheet1!A1:A22,worksheet1!B1:B22)

    But its not working for me, I want to be able to look up a row in my ingredients column on spread sheet one and link it to a customer on my customer column and have that link to work sheet 2 (if that makes sense) I also want to be able to add new rows and columns to worksheet 1 without messing up my formulas in other worksheets (so using words as the search cretira to find the measurement numbers?)

    Not sure if any of this makes sense but any help is greatly appreciated!

    Kindly,
    Anna

    1. Hello Anna!
      I used your formula in my worksheet. She works. It extracts the value from column B. You probably wanted to get some other result. But I could not understand which one.
      I also recommend converting your worksheet1! A1: A22 and worksheet1! B1: B22 columns to named ranges or to a table, as described in the article above. This will allow you not to worry that your links will break when adding data.
      For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find, and what problem or error occurred. It’ll help me understand it better and find a solution for you. Thank you.

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

  31. How to do hyper link for multiple sheet

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

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

  34. Thanks Svetlana & Keep it up Dear.

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