This week I had a question from Diedre asking if she can use VLOOKUP to check multiple sheets…. 17 different sheets in fact.
The idea being that if VLOOKUP doesn’t find a match on the first sheet, it will check the next sheet and so on.
The good news is we can, the bad news is it’s a bit complicated….but if you’ve only got a few sheets I‘ll show you an easier formula at the end.
Below is our table that we want to populate by looking up the Product Code in column A and return the Product Description and Price.
The challenge is that the product code information could be on Sheet2:
Or Sheet3:
Or Sheet4:
The first thing we need to do is enter a list of our sheet names somewhere in the workbook and give them a named range. I’ll give mine the very imaginative name: SheetList
Now we can enter our formula in cell B4:
=VLOOKUP(A4,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$A$1:$c$4"),A4)>0),0))&"'!$A$1:$c$4"),2,FALSE)
It’s an array formula so we need to enter it with CTRL+SHIFT+ENTER.
We can then copy it down the column to lookup the remaining product codes.
The above is just a small sample of the toys on my boy’s Christmas lists!
How this Formula Works
Unfortunately this formula doesn't evaluate in order from left to right, so it’s a bit difficult to translate into English as I like to do, instead we’ll look at the separate components and understand what they’re doing.
First remember the syntax for the VLOOKUP Function is:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
In our formula we know everything except the table_array argument. Remember we don’t know what sheet contains our lookup_value.
Instead we use the INDIRECT, INDEX, MATCH and COUNTIF functions to build the table_array reference dynamically for each product code in column A like this:
=VLOOKUP(A4,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$A$1:$c$4"),A4)>0),0))&"'!$A$1:$c$4"),2,FALSE)
1. The COUNTIF component
The COUNTIF part of the formula is checking each sheet in the SheetList to find a match. If it finds a match it counts 1, the other sheets return a zero.
=VLOOKUP("SKU001",INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT ("'"&SheetList&"'!$A$1:$c$4"),"SKU001")>0),0))&"'!$A$1:$c$4"),2,FALSE)
Our formula now looks like this:
=VLOOKUP("SKU001",INDIRECT("'"&INDEX(SheetList,MATCH(1,{1;0;0},0))&"'!$A$1:$c$4"),2,FALSE)
The {1;0;0} above represents the count of matches on the 3 rows in the SheetList. You can imagine because it found a match for SKU001 on Sheet1 it looks a bit like this:
2. MATCH Function
The syntax for the MATCH function is:
MATCH(lookup_value, lookup_array, [match_type])
From our formula: MATCH(1,{1;0;0},0)
Where zero [match_type] is an exact match.
The MATCH function is looking for the value 1 in the lookup_array {1;0;0} and returns its position. Which in this case is the first position, therefore our MATCH formula evaluates to 1 like this:
=VLOOKUP("SKU001",INDIRECT("'"&INDEX(SheetList,1)&"'!$A$1:$c$4"),2,FALSE)
Note: if it found a match on Sheet3 it would look like this:
MATCH(1,{0;1;0},0) and would evaluate to 2. i.e. the second value in the SheetList.
3. The INDEX function
INDEX Function syntax:
INDEX(reference, row_num, [column_num], [area_num])
Note: The last two arguments are optional (designated by the square brackets) and we don’t need them in this formula.
INDEX(SheetList,1)
The INDEX function uses the result from MATCH as the row number argument and returns a reference to the 1st value in the SheetList; Sheet2
Our formula now looks like this:
=VLOOKUP("SKU001",INDIRECT("'"&"Sheet2"&"'!$A$1:$c$4"),2,FALSE)
4. The INDIRECT Function
The INDIRECT Function returns a reference specified by a text string.
Everything inside the INDIRECT formula above is text, you can tell by the double quotes surrounding it. The INDIRECT function takes that text and converts it to a reference.
COUNTIF, INDEX and MATCH have done the heavy lifting, now all INDIRECT needs to do is add the necessary apostrophes to the reference and give it to VLOOKUP.
It’s a bit difficult to see the apostrophes from the double quotes so I’ve made them red below:
=VLOOKUP("SKU001",INDIRECT("'"&"Sheet2"&"'!$A$1:$c$4"),2,FALSE)
Which becomes:
=VLOOKUP("SKU001",'Sheet2'!$A$1:$c$4,2,FALSE)
VLOOKUP Multi-cell Array Formula
Now we’ve found the product descriptions we could copy the formula into column C and change the col_index_num argument, or as Finn, my 4 year old, says ‘we could be a bit more cleverer’ and change the formula slightly so that it can find both results with the one formula!
To do this we’ll use a multi-cell array formula.
When you enter a multi-cell array formula you first select all the cells you want populated, then you enter the formula.
Since we’ve already got our formula in cell B4 we can select B4 and C4, then press F2 to edit the formula.
All we need to change is the col_index_number argument like this:
=VLOOKUP(A4,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT ("'"&SheetList&"'!$A$1:$c$4"),A4)>0),0))&"'!$A$1:$c$4"),{2,3},FALSE)
Then press CTRL+SHIFT+ENTER and you now have your price information and you have used the same formula in both B4 and C4.
Now you can copy cells B4 and C4 down columns B and C and you’re done 🙂
The Easier Option
OK, if your head is hurting here’s an easier solution that will do if you’ve only got 2 or 3 sheets to lookup.
Warning: Any more than 3 sheets and you’re susceptible to a repetitive strain injury so it’s best to use the first solution.
VLOOKUP Multiple Sheets with IFERROR
All we do is wrap the VLOOKUP formulas in an IFERROR function.
=IFERROR(VLOOKUP($A4,Sheet2!$A$2:$C$4,2,FALSE),IFERROR(VLOOKUP($A4,Sheet3!$A$2:$C$4,2,FALSE),VLOOKUP($A4,Sheet4!$A$2:$C$4,2,FALSE)))
In English it reads:
Lookup the product code in A4 on Sheet2 and return the value in column 2 of the range A2:C4, if you can’t find an exact match in Sheet2 check Sheet3, if you can’t find an exact match in Sheet3 check Sheet4.
VLOOKUP Dynamic col_index_num
The formula above is good, but we want to copy it to column C for the price and in doing so we’d need to change the col_index_num argument.
An easier way to do this is to make it dynamic by using the COLUMNS function for the col_index_num argument like this:
=IFERROR(VLOOKUP($A4,Sheet2!$A$2:$C$4,COLUMNS($A$3:B$3),FALSE),IFERROR(VLOOKUP($A4,Sheet3!$A$2:$C$4,COLUMNS($A$3:B$3),FALSE),VLOOKUP($A4,Sheet4!$A$2:$C$4,COLUMNS($A$3:B$3),FALSE)))
The COLUMNS function returns the number of columns in an array or reference.
A3:B3 = 2 columns in the range.
Therefore COLUMNS($A$3:B$3) evaluates to 2 which is the col_index_num we want.
Note how the first cell in the range $A$3 is absolute but the second cell in the range is only absolute for the row number.
Now when I copy the formula across to column C my formula dynamically updates like so:
=IFERROR(VLOOKUP($A4,Sheet2!$A$2:$C$4,COLUMNS($A$3:C$3),FALSE),IFERROR(VLOOKUP($A4,Sheet3!$A$2:$C$4,COLUMNS($A$3:C$3),FALSE),VLOOKUP($A4,Sheet4!$A$2:$C$4,COLUMNS($A$3:C$3),FALSE)))
And COLUMNS($A$3:C$3) evaluates to 3.
If you liked this click the buttons below to share it with your friends and colleagues on Facebook, Twitter, Google+ or LinkedIn....or leave me a comment 🙂
Jaimal Patel says
Hello
the formula =VLOOKUP(A4,INDIRECT(“‘”&INDEX(SheetList,MATCH(1,–(COUNTIF(INDIRECT (“‘”&SheetList&”‘!$A$1:$c$4″),A4)>0),0))&”‘!$A$1:$c$4”),{2,3},FALSE) althought complicated at first I understood it step by step. I just wanted to know if you had more variables than than just description and price, say 6 or 7 in total would the columns be {2,3,4,5,6,7,…} depending on the number of headings?
also the reference would change $A$1:$c$4 if you had more headings?
I just wanted to know would the formula change slightly with more sheets and more headings/(variables?
Thanks
Jaimal Patel
L.E.:
sry I meant, 6 or 7 sheets instead,as well as more headings?
Catalin Bombea says
Hi Jaimal,
You have to upgrade you data structure to a more flexible format, seems like your file has the CFN syndrom (Complex Formulas Needed), this happens usually when source data is split into multiple sheets, for visual and printing purposes. If you decide to upgrade your structure, you will be able to use the most powerful excel tool for data processing and reporting.
You can try the new support forum, if you upload a sample file with your data structure, we will certainly find new solutions for you.
Here is the link to our forum: myonlinetraininghub.com/forum
Catalin
Jaimal Patel says
Thankyou Catalin
Jaimal Patel
Prem Singh says
Dear Madam,
refer to your sheet is it possible to get the first occurrence of an id (by time at what time he entered first) or use some formulas/functions to do so.
Catalin Bombea says
Hi Prem,
Can you please upload a sample file on our forum? Here is the link to the forum: http://www.myonlinetraininghub.com/forum
Catalin
Prem Si says
yes ma’am I can upload the same.
know there is another question in mind related to your vlookup with multiple sheets.
Is it possible to use match function at col_index_num as I am using formula but it now works. =IFERROR(VLOOKUP($K2,INDIRECT(“‘”&INDEX(range,MATCH(1,–(COUNTIF(INDIRECT(“‘”&range&”‘!I:I”),$K2)>0),0))&”‘!I:O”),MATCH(1,–(COUNTIF(INDIRECT(“‘”&range&”‘!I:O”),$K2)>0),0),0),””) is there any way to do it such as we can do this in simple vlookup
Catalin Bombea says
Hi Prem,
The need for complex formulas is a known disease, generated by a poor data structure. To get your data well, and improve its health, you have to change it to a more flexible data structure. If the structure is correct, there is no need for complex formulas. Not to mention that you will be able to use more powerful built in tools to analyze the data. If you have same data structure in all those sheets, then the best choice is to reformat them with Power Query. Only then you will see the real power of modern excel.
You can upload a sample file on our new forum, we can help you find a solution there: myonlinetraininghub.com/forum
Catalin
Mark says
How can I adjust this approach for the following?:
I have the same lookup criteria that I want to find on every other sheet in my workbook. How can I adjust this formula to display the maximum result found amongst all the sheets? The term being looked up could appear in any row on the later sheets (I haven’t built the sheet yet or I’d give specific references). Can it be done with just INDEX – MATCH formulas instead of VLOOKUP?
Catalin Bombea says
Hi Mark,
Can you please upload a sample file on our Help Desk? (create a new ticket). It will be easier for us to understand how the data is structured and provide a personalized answer to help you.
Catalin
Rmango says
This is all very helpful.
However, what do we do if the length of data (the number of rows) in each sheet is different than the other. Sheet 1 has 300 rows, sheet 2 has 140 rows, sheet 3 has 1000.
Will the formula change
=VLOOKUP(A4,INDIRECT(“‘”&INDEX(SheetList,MATCH(1,–(COUNTIF(INDIRECT(“‘”&SheetList&”‘!$A$1:$c$1000″),A4)>0),0))&”‘!$A$1:$c$1000”),2,FALSE)
I am assuming that I will go with the largest number of rows. but then that would mean that i will have empty rows in the other sheets.
Please help!
Mynda Treacy says
Hi Rmango,
Correct. Just adjust your formula range to fit the largest number of rows. It doesn’t matter if some sheets have blank rows in that range.
Mynda
JMAGGOLS says
I AM HAVING AN ERROR IN DUPLICATNG THE FORMULA. ALTHOUGH THESE ARE DIFFERENT WORKBOOKS, THEY WILL BE OPEN WHEN PERFORMING THE LOOKUP.
I AM GETTING THE MESSAGE THAT THERE ARE TOO MANY OR TOO FEW ARGUMENTS.
COULD YOU PLEASE ASSIST? BELOW IS THE FORMULA I AM USING.
=IFERROR(VLOOKUP($A2,'[ASCEND template FY Q1 2016.xlsx]6. Payout by worked hrs’!$A$1:$Y$4314,11,FALSE),IFERROR(VLOOKUP($A2,'[ASCEND template FY Q2 2016.xlsx]6. Payout by worked hrs’!$A$1:$Y$4314,11,FALSE),IFERROR(VLOOKUP($A2,'[ASCEND template FY Q3 2016.xlsx]6. Payout by worked hrs-Q3-16 ‘!$A$1:$Y$4314,11,FALSE), IFERROR(VLOOKUP($A2,'[ASCEND template FY Q4 2016.xlsx]6. Payout by worked hrs’!$A$1:$Y$4314,11,FALSE),IFERROR(VLOOKUP($A2,'[ASCEND template FY Q1 2017.xlsx]6. Payout by worked hrs’!$A$1:$Y$4314,11,FALSE))))
Catalin Bombea says
Hi,
Your formula should have a value for the last IFERROR, the formula should end like this:
IFERROR(VLOOKUP($A2,'[ASCEND template FY Q1 2017.xlsx]6. Payout by worked hrs’!$A$1:$Y$4314,11,FALSE),””))))
You can upload a sample file on our Help Desk, (create a new ticket), if you need more help on this problem.
Catalin
Jim C. says
Not sure if you are still monitoring this thread. I came across your solution while working on a summary sheet that has to pull and display a value for the “current month” from one of 12 additional sheets in the workbook. Each of the 12 additional sheets contains data for one month of the year. The “current month” value in my summary sheet changes each month automatically based on the “system-month.”
I tried using your formula to get Excel to go find the sheet with the name matching the “current month” and then look up a value in the appropriate monthly sheet. The value I am looking up is located in the same cell in every sheet.
Unfortunately, it doesn’t work. In debugging it gets partly through, identifying the month I am looking for, looking at my SheetList, but then I get a #VALUE! error. Here’s a copy of your formula follow by my formula reflecting my values.
VLOOKUP(A4,INDIRECT(“‘”&INDEX(SheetList,MATCH(1,–(COUNTIF(INDIRECT(“‘”&SheetList&”‘!$A$1:$c$4″),A4)>0),0))&”‘!$A$1:$c$4”),2,FALSE)
VLOOKUP($AA$41,INDIRECT(“‘”&INDEX(SheetList,MATCH(1,–(COUNTIF(INDIRECT(“‘”&SheetList&”‘!$AC$4:$AN$4″),A4)>0),0))&”‘!$AC$4:$AN$4”),4,TRUE)
I thought the problem was the A4 in the last third of the formula. Assuming it was supposed to be the same value as the value in the first A4 in your formula, I changed mine to $AA$41. I still get the same error.
I tried simplifying and doing a straight vlookup with the name of the specific sheet embedded in the lookup and that works. But I need the INDIRECT function to find the correct month so I don’t have to update my formula everything month.
L.E.: One additional thing… I did hit Ctrl, Shift, Enter and then I get an #NA… regardless of which cell reference I use.
Can you help?
Thank you.
Jim
Catalin Bombea says
Hi Jim,
Can you please upload the file on our Help Desk System? You can create a new ticket, it will be easier to find an answer to your problem.
Cheers,
Catalin
Maurice says
Do the spreadsheets you are searching have to be in the same workbook?
How would you set the array to look up spreadsheets in different folders?
Thanks,
Maurice
Mynda Treacy says
Hi Maurice,
INDIRECT won’t work on worksheets in closed external workbooks. For this reason I don’t recommend this method. It would be much safer to consolidate your data first.
Kind regards,
Mynda
Maurice says
Thank you for your reply. I think you’re right – I should consolidate all of my different .xlsx files into one workbook with several tabs across the bottom.
Then Incan run your INDIRECT script on all of this data.
How do I consolidate many files in different folders into one workbook?
Thanks,
Maurice.
Mynda Treacy says
Hi Maurice,
Actually, I’d even consolidate the separate worksheet tabs into one. You’ve got over 1M rows so unless you’ve got masses of data then it should be in one worksheet.
This is how all of Excel’s tools were designed to work: PivotTables, functions etc. It’s when you start splitting data out that you run into problems, as you’ve found. Using INDIRECT, while it works, is really not the right way to summarise your data, it’s like putting a band-aid on.
I write about the ideal data format here: http://www.myonlinetraininghub.com/excel-tabular-data-format
You can use Power Query to consolidate data, or you can get an add-in to consolidate your worksheets and workbooks, or you can do it the manual way and copy and paste.
I hope that helps point you in the right direction. Let me know if you have any questions.
Mynda
Binoy Shah says
Dear Sir,
I am trying to solve below query but message is display #NA. Please guide me now on my mail.
=VLOOKUP(AND($G$6,$H$6),’Vacant Only’!$A$4:$N$221,14,FALSE)
Mynda Treacy says
Hi Binoy,
You can’t use AND like that. I presume what you want may be:
But without seeing your data it’s a guess. This tutorial might have the answer:
http://www.myonlinetraininghub.com/vlookup-multiple-values-in-multiple-columns
or this one:
http://www.myonlinetraininghub.com/vlookup-multiple-values
If none of those help then you can send a sample file via the Help Desk.
Kind regards,
Mynda
Binoy Shah says
Sheet1
G6 H6
Inst_Code course_code
4 16
4 16
4 16
Sheet2
A5 C5 N5
Inst_Code course_code Vacant
4 16 1
4 16 1
4 16 1
I use =VLOOKUP($G$6&$H$6,’Vacant Only’!$A$4:$N$221,14,FALSE) then Answer is display #NA. Please guide me.
Mynda Treacy says
Hi Binoy,
You need this formula:
Entered as an array formula with CTRL+SHIFT+ENTER
Explained here under the heading ‘VLOOKUP vs INDEX & MATCH’:
http://www.myonlinetraininghub.com/vlookup-multiple-values-in-multiple-columns
An Excel file submitted via the help desk would be easier to answer as I could refer to cell references.
Kind regards,
Mynda
Binoy Shah says
Sheet1
Inst_Code course_code
4 16
4 16
4 16
4 16
4 17
4 17
4 22
4 22
4 23
4 28
4 28
4 29
Sheet2
Inst_Code Course_Code Vacant
4 16 1
4 16 1
4 17 3
4 17 2
4 23 2
4 29 1
=INDEX(‘Vacant Only’!$A$4:$N$221,(MATCH(‘Vacant Only’!$G$6&$H$6,’Closer of OPEN CAt Final’!$G$6&$H$6,0)))
As per your guide lite I use this formula in my Excel sheet but Error message showing #NA.
Please guide me. Please reply as early as possible in my mail as well in post.
Mynda Treacy says
Binoy,
The first argument of INDEX should only be one column, the column containing the data you want to return i.e. ‘Vacant’. Then also make sure you enter the formula by pressing CTRL+SHIFT+ENTER.
This is an array formula. It should have curly braces surrounding it after entering, which are only visible in the formula bar.
If you continue to have problems please do not reply here. Post your question with supporting Excel file via the Help Desk.
Mynda
Binoy Shah says
Still I am waiting for your reply. Please give answer urgently.
Mynda Treacy says
I have replied, but I can’t give you a specific answer without a specific example in a workbook.
Mynda
Nigelbloomy says
First of all this formula is awesome. Along with your 3D SUMIF, this is amazing. Someone asked in Excelforum.com if there was a way to expand this to include multiple criteria. I figured that out by changing your COUNTIF to a COUNTIFS. I just wanted to post this here in case anyone else has the same question of how to lookup multiple criteria.
I put the single quotes and the exclaimation point in the text of the list to make the formula a little easier to read.
=VLOOKUP(A2,INDIRECT(INDEX(Sheets&"$A$1:$C$1000",MATCH(1,COUNTIFS(INDIRECT(Sheets&"$A$1:$A$1000"),A2,INDIRECT(Sheets&"$B$1:$B$1000"),B2),0))),3,0)Mynda Treacy says
Thanks, Nigel. Appreciate you sharing your formula too.
Cheers,
Mynda
Tiamari says
I’ve been searching and searching for a solution, and here it is! FINALLY!!! You are AMAZING!!!!
Mynda Treacy says
🙂 Thanks! Glad I could help.
Jim says
Hi Mynda,
I am not sure what happened to my post from earlier today but thank you for responding. My original post asked about using the formula above in a way to look across 35 sheets 1st for the date in cell A3 and then when it finds the right date it then pulls the sales, traffic, conversion etc.
You mentioned that it would be very hard to do this and I should try putting everything in tabular data format. However, the only problem with that is that there is a lot of other information on each sheet that is needed and cannot be put into tabular form (not all numbers). Could I lead with the find function to find the date?
Again, thanks for your help.
Mynda Treacy says
Hi Jim,
A Tabular data set can have numeric and text data. It really is the ‘only’ way you should store data for use in formulas in Excel. Any other way and you just run into problems as you are now.
But to answer your question, looking up a date is no different to looking up any other value/text. You just reference the cell containing the date you want to lookup and then reference the range of cells containing the dates. If it’s not working then I suspect there is an inconsistency with your date formats. I can’t tell without the workbook but you can send it to me via the Help Desk.
BTW, your comment in at the bottom of the blog post where you originally entered it.
Kind regards,
Mynda
Jim says
Thank you again for taking the time to reply, I really appreciate it.
I am going to try that and a few more ideas and see where it takes me. If I still run into the problem then maybe send it to you via the help desk.
Yes, sorry about the double comment. For some reason when I was looking at the page my original response was not at the bottom top or middle. Even did Control F and searched by name and nothing. Then after submitting the new post it popped up and was there. Lastly, thank you for all the videos and webinars on dashboards…they have really helped me at work!
Mynda Treacy says
You’re welcome, Jim.
Jim M says
Hi,
This might be what i have been looking for, however still a little confused.
I have 35 (1 for each day and 1 weekly conversion tool) worksheets that consist of sales data and some other daily information. I want to create a monthly worksheet that will auto populate each night once the sales have been entered.
On my monthly overview sheet, I have day of the week and date listed. Is there a way that i can use this formula to first search all 35 sheets for the date and when it finds the correct date it then pulls the sales info?
Thanks for any help!
Mynda Treacy says
Hi Jim,
You could use this formula but the best thing you can do is put all your data onto one sheet. You are making Excel work extra hard (and yourself) by formatting your data across 35 sheets.
I recommend you read this tutorial on Tabular Data. Tabular data is the secret to an easy Excel life 🙂
Mynda
George Mitchell says
Oh my head is going to explode and my eyes have glazed over. How in the world do you keep your sanity?
Mynda Treacy says
😀 Sorry about that, George. Sometimes banging your head on the desk gives some light relief!
Daniel Smith says
Brilliant formula!
Can this be used when I have all the data in one workbook and the values I am matching against are in another?
I tried to incorporate the path name in front of the named range but this just created an error with two few arguments.
Thanks
Catalin Bombea says
Hi Daniel,
Unfortunately, you can’t use names to reference ranges in closed workbooks.
Catalin
Michael Connors says
You can use IFERROR with Index Match formulas — something like this
IFERROR(INDEX(‘Sheet2’!$B$2:$B$4,MATCH($A$4,’Sheet2 ‘!$A$2:$A$4,FALSE),0),IFERROR(INDEX(‘Sheet3’!$B$2:$B$4,MATCH($A$4,’Sheet3!$A$2:$A$4,FALSE),0),IFERROR(INDEX(‘Sheet4′!$B$2:$B$4,MATCH($A$4,’Sheet4’!$A$2:$A$6,FALSE),0),0)))
Mynda Treacy says
Hi Michael,
That’s an INDEX & MATCH version of the ‘VLOOKUP Multiple Sheets with IFERROR’ option above.
Thanks for sharing.
Mynda
Michael Connors says
Thank you for your kind words
Another way of doing it is with If(ISERROR Here’s the VLookup version with IF(ISERROR
=IF(ISERROR(VLOOKUP($A$4,’SHEET2′!$A$2:$C$4,2,FALSE)),IF(ISERROR(VLOOKUP($A$4,’SHEET3′!$A$2:$C$4,2,FALSE)),IF(ISERROR(VLOOKUP($A$4,’SHEET4′!$A$2:$C$4,2,FALSE)),0,(VLOOKUP($A$4,’SHEET4′!$A$2:$C$4,2,FALSE))),(VLOOKUP($A$4,’SHEET3′!$A$2:$C$4,2,FALSE))),(VLOOKUP($A$4,’SHEET2′!$A$2:$C$4,2,FALSE)))
The IFERROR version goes from left to right, while the IF(ISERROR version goes from the center out.
Steven Kfare says
Hi,
Is there a cut-off point with the number of sheets that method one becomes not optimal to use (i.e. 10 sheets with 100k rows each)? At that point would it be better to use a macro or some other method?
Mynda Treacy says
Hi Steven,
I haven’t tested the formula on large volumes of data so it’s hard to say. One thing I would consider is consolidating your data into one sheet, as it should be.
This formula is necessary because the data isn’t stored in a single tabular format. It’s spread over multiple sheets which is a no-no.
Kind regards,
Mynda
Steven Kfare says
Unfortunately,the data that I am trying to match up against is larger than the Excel row limit therefore I needed to break out to multiple sheets.
Mynda Treacy says
Ah, sounds like time for a database then. How about putting your data in Access and using PivotTables to summarise it?
Mynda
Robert Haynes says
Hello, I see this is an old article, but I was hoping you could help me…
I have a formula that searches 1 worksheet in the document and displays all rows that which meet a certain criteria for 1 of 3 columns.
=IFERROR(INDEX(HHBN!B$2:B$622,SMALL(IF(HHBN!$E$2:$E$622=$C$1,ROW(HHBN!B$2:B$622)-ROW(HHBN!B$2)+1,IF(HHBN!$M$2:$M$622=$C$1,ROW(HHBN!B$2:B$622)-ROW(HHBN!B$2)+1,IF(HHBN!$AC$2:$AC$622=$C$1,ROW(HHBN!B$2:B$622)-ROW(HHBN!B$2)+1))),ROWS(HHBN!B$2:HHBN!B2))),””)
I’m trying to incorporate your idea here of searching through all four tabs, but I can’t get it to work. Thank you for any help you can give.
Catalin Bombea says
Hi Robert,
Please upload a sample workbook with your data structure and details on what are you trying to achieve. It’s almost impossible to see why your formula is not working. You can use our Help Desk.
Catalin
Robert Haynes says
Catalin,
Thank you for the response. The formula I posted works how it is suppose to. I was hoping to use the idea in this post to adjust it so it will search all tabs instead of just one, but I can’t figure it out. I’ll try to upload it to the help desk. Thank you again for your response.
Robert
Catalin Bombea says
Ok Robert, i’ll wait for that file and details, we’ll find a solution for you.
Cheers,
Catalin
Randy Vargas says
since i was high school and colleges, I only now we learn this vlookup but it’s difficult when you getting started until the next steps so we need more time to learn and study this vlookup.
Mynda Treacy says
Hi Randy,
It’s all about practice. The more you do it the easier it will get.
Cheers,
Mynda.
Joe Stephens says
Hi Mynda –
This is similar to a challenge I am experiencing – with a little twist.
I am trying to lookup data in a Workbook based on the value of two cells in a Worksheet.
Here is the example:
Worksheet ‘A’, cell C8 contains the letter “P”.
Workbook ‘B’ contains 26 worksheets – A thru Z.
1. Go to the worksheet in Workbook ‘B’ based on the letter in cell C8.
2. Lookup the value of cell D8 in the same worksheet.
3. Array:Workbook ‘B’, Worksheets A-Z,$C:$H,6,0
I would appreciate any assistance you can offer.
Thanx!
Catalin Bombea says
Hi Joe,
Please upload a sample workbook on Help Desk
This way we can work on a real data structure, and the result is not just an idea.
Thank you for understanding.
Catalin
Nuzry says
Try this
=INDIRECT(“‘[WorkbookB.xlsx]”&C8&”‘!$d$8:$D$8”)
jinal says
sheet1 in total then sheet 2 in total then i give a sheet1+sheet2 total in sheet3 plz ans me with example plz
Catalin Bombea says
Hi Jinal,
For example, if the total value for sheet1 is in cell E14, and same for sheet2, you can use a 3D SUM to add the totals from sheets 1 and 2:
=SUM(Sheet1:Sheet2!E14)
If it’s not what you wanted, please use our Help Desk to upload a sample workbook with detailed description of what you are trying to achieve.
Catalin
Robert Singh says
Superb! Wow!
Mynda Treacy says
Thanks, Robert 🙂
Ajeshkumar says
Very useful step by step explanation, even the beginners can do well
Mynda Treacy says
Thanks, Ajeshkumar 🙂 Glad you liked it.
Mynda.
Hasan says
Hi Mynda,
This is very usefull. But along the same lines I would like to use similar formula to access the data from different excel reports (stored in various folders) instead of sheets in a same workbook.
For examples, my files are saved here:
c:\2013\Nov13\a.xls
c:\2013\Nov13\b.xls
c:\2013\Nov13\c.xls
c:\2013\Nov13\d.xls
Formula:
=VLOOKUP(BI20,INDIRECT(“‘”&INDEX(SheetList3,MATCH(1,–(COUNTIF(INDIRECT(“‘”&SheetList3&”‘!$A$1:$c$9″),BI20)>0),0))&”‘!$A$1:$c$9”),2,FALSE)
SheetList3:
c:\2013\Nov13\a.xls
c:\2013\Nov13\b.xls
c:\2013\Nov13\c.xls
c:\2013\Nov13\d.xls
I tried using the above entries in a SheetList but the formula didn’t work. How should I do this ? Do I need to put the file name in double/single quotes, etc. ? I’ve tried everything but it didn’t work.
Thanks
Hasan
Catalin Bombea says
Hi Hassan,
Please provide a sample of your workbooks, so i can test on your environement, this way you will have a faster solution.
You can use the Help Desk: http://www.myonlinetraininghub.com/helpdesk/
Cheers, Catalin
Rajanz says
Hi, i have a workbook with 6 sheets, each sheet has 15 colums & approx 6000 rows of data, so i want to run a vlookup for a specific criteria but the consition is that the formula should check all the 6 sheets before giving the result, is that possible??
Mynda Treacy says
Hi Rajanz,
Yes, it’s possible using the formula in the blog post above. Note: it will only work if the match is only one one sheet in your workbook. If you have multiple matches it won’t return the correct result.
Kind regards,
Mynda.
Rajanz says
Hi Mynda,
I tried applying the formula to my worksheet but it doesnt return any value, i unable to debug the error, would it possible to look at the sheet & suggest a solution?
Mynda Treacy says
Hi Rajanz,
You can send me the workbook via the help desk and I’ll take a look. Please include specific instructions on what you’re trying to do and give me an example of your expected result.
Thanks,
Mynda.
Chase says
I am using this formula to look over many pages and for some reason it works on some of the cells but others it either returns a value of “#N/a” or “0”. What is the difference between the two?
Mynda Treacy says
Hi Chase,
A #N/A error means the lookup_value cannot be found. A zero means it found the lookup_value but the cell you asked it o returned was either empty or contained a zero.
I hope that helps.
Kind regards,
Mynda.
Kevin says
I read the previous post.. and success! sorry to bother. Thanks 🙂
Mynda Treacy says
Cheers, Kevin. Glad you figured it out.
Kevin says
Hey, I understand about 70% of this and am still re-reading it to fully understand. Is there a way however to use this formula.. but refer to a seperate workbook for the alternating sheets?
Brad Firsich says
Topic: Excel Lookup, Multiple Sheets
All we need to change is the col_index_number argument like this:
=VLOOKUP(A4,INDIRECT(“‘”&INDEX(SheetList,MATCH(1,–(COUNTIF(INDIRECT (“‘”&SheetList&”‘!$A$1:$c$4″),A4)>0),0))&”‘!$A$1:$c$4”),{2,3},FALSE)
How does the above formula evaluate {2,3}?? I have always typed the column number I needed.
Thanks in advance.
P.S. Your website is fantastic!!
Mynda Treacy says
Hi Brad,
Thanks for your kind words. Glad you like our site 🙂
The formula you’re referring to is a multi-cell array formula. i.e. the formula is entered in multiple cells all at once.
Because we’re entering the same formula in both columns (Product description & price), we need to give it the col_index_number for both columns. This is the {2,3} information in the formula (known as an array constant). The array formula knows to use 2 for the Product Description column and 3 for the Price column.
I hope that helps.
Kind regards,
Mynda.
Steve says
Mynda,
Thanks for this formula! I was able to get it to work when everything is in the same workbook, but the application I’m using it for has so many sheets that I want to separate them into two workbooks. I can’t get the reference to work. Basically, this is what I have:
=VLOOKUP($B3,INDIRECT(“‘”&INDEX(‘[workbook2.xlsx]Sheet1’!SheetList,(MATCH(1,–(COUNTIF(INDIRECT(“‘”&'[workbook2.xlsx]SheetList’!SheetList&”‘!$c$8:$d$8″),$B3)>0),0)))&”‘!$c$8:d$8”),2,FALSE)
Any suggestions?
Mynda Treacy says
Hi Steve,
Does your SheetList contain the workbook name as well as the sheet name?
INDIRECT is deriving the range to be looked up but since it’s in another workbook it also needs the worksheet name, not just the sheet name.
Kind regards,
Mynda.
Steve says
Thanks for the reply. I thought of that, so I tried with an unnamed list as well, and just dragged a copy of the sheet over to a new workbook so it would update the address automatically. So the list started out as:
Students!$B$3:$B$34
…and became:
‘[Equipment Checkout.xlsx]Students’!$B$3:$B$34
Here, “Students” is the worksheet with the list of sheet names on it (from B3:B34), each sheet is named after a student, and “Equipment Checkout” is the workbook that contains the sheets. When I drag one of the equipment inventories (Sleeping Bags, for example) over to a new book, it changes the list address as above, but I still get #n/a. Just can’t figure this one out.
Thanks for your help.
Steve says
I thought about your response and tried something new, still to no avail. I put the workbook name “Equipment” in cell B2 and changed the list to B2:B34. I still get the #n/a error.
Is that what you meant?
Mynda Treacy says
Hi Steve,
I got this formula to work:
=VLOOKUP(A7,INDIRECT(“‘”&INDEX(Book3!sheetlist,MATCH(1,–(COUNTIF(INDIRECT(“‘”&Book3!sheetlist&”‘!$A$1:$c$4″),A7)>0),0))&”‘!$A$1:$c$4”),3,FALSE)
Where my ‘sheetlist’ is in Book3.xlsx. How does that compare to what you’ve tried?
Mynda.
Steve says
I tried that and still had trouble, but I did find a solution after thinking about your last post! Like you did, I put the workbook in the formula, and all I had to do was put the workbook location in front of every item on the sheetlist. So instead of just sheet1, sheet2, etc. the list looks like Drive:Folder:[workbook.xlsx]sheet1.
Now it works perfectly. Thanks for your help!
Mynda Treacy says
Brilliant! Glad you got there in the end 🙂
Steven D'hoe says
Hi Mynda,
Slight variation… how do I check where a value is present in multiple sheets?
Your IFERROR/VLOOKUP solution stops at soon as it find a hit. I want to keep going and make sure I find another hit.
I’ve tried COUNTIFS, VLOOKUPS, SUMPRODUCT and MATCH… but they don’t work as the value I’m looking for is not always in the same position in both tables, ie. the code could be the 2nd row in the first sheet/array and in the 18th row in the second sheet/array.
Please help… can’t find the answer.
Thanks,
Steven.
Mynda Treacy says
Hi Steven,
Do you want to sum these multiple instances of a match? If so the 3D SUMIFS might be what you’re after.
If that’s not it please send me a sample file via the help desk so I can see what you’re working with.
Cheers,
Mynda.
ChrisS says
Mynda,
This is super helpful but how would I edit this if the sheets I’m trying to access are in another workbook? I understand indirect doesn’t work unless the other workbook is open (unless I download the add-on that lets you do so). Any help would be greatly appreciated!
ChrisS says
Woops never mind, just read the comment above!
Mynda Treacy says
🙂 Cheers, Chris.
Jim says
Hi this is great and really helpful but currently i have a vlookup to search another workbook which has ever increasing amounts of rows.
The number of rows have reach their maximum so now it is recording data on a second sheet.
My question is can you use this to look up multiple sheets on another workbook?
thanks in advance
jim
Mynda Treacy says
Hi Jim,
If you’re running out of rows it sounds like it’s time to put all that data into Access.
I imagine you can use that formula across multiple workbooks but you’d have to add the workbook name in front of the worksheet name so that the INDIRECT function had the correct cell address.
The reference would look a bit like this:
Where the workbook name is inside [square brackets].
Also, the formula will only work when both workbooks are open. INDIRECT doesn’t work if referencing a closed workbook.
I hope that helps. Let me know if you get stuck and I’ll take a look.
Kind regards,
Mynda.
Shyam says
Really helpful, lucid, attractive and cool…
Mynda Treacy says
Thank you, Shyam 🙂
Mynda Treacy says
Thanks, Shyam 🙂
Rodolfo says
Thank you. It worked quite well; however, my spreadsheet has 50 worksheets & the formula works only for the the 1st ten, although the the named range I created contains all the 50 worksheets. Is there any way to make it check all the 50 worksheets?
Appreciate your help.
Mynda Treacy says
Hi Rodolfo,
Not sure why it wouldn’t work for all 50 sheets. Are you able to send me the file via the help desk or is it too big?
Cheers,
Mynda.
MF says
That is brilliant.
Could you pls further explain what’s the purpose of –(,>0)?
As I find that the formula seems working properly without it; but interestingly if I change input the formula as array with Column Index {2,3}, it doesn’t work…
So what is the magic of the –??
Appreciate your explanation! 🙂
Mynda Treacy says
Hi MF,
Well spotted. If you don’t use the >0 test you can also omit the double unary –, so the formula would become:
=VLOOKUP(A4,INDIRECT("'"&INDEX(SheetList,MATCH(1,(COUNTIF(INDIRECT("'"&SheetList&"'!$A$1:$c$4"),A4)),0))&"'!$A$1:$c$4"),2,FALSE)The double unary converts TRUE/FALSE outcomes (resulting from the >0 test) to 1’s and 0’s so they can be used in the MATCH part of the formula. By leaving out the >0 the COUNTIF evaluates to numbers, so the double unary isn’t required.
I hope that helps.
Kind regards,
Mynda.
PARAM says
THANKS A LOT
IT IS VERY GOOD EXAMPLE AND MUCH USEFUL DON’T KNOW ANY THING PEOPLE ALSO.
thanks
Param
Mynda Treacy says
Cheers, Param 🙂
SILAMBARASAN.S says
Dear Sir/Madam,
I would like to learn “Vlookup” formula.. i have preparing many statment, it is take more time to do and completed, if i know how to use the vlookup formula into it, it will help us to time saving. The problem is No. 1. For Example: while the employees resigned the job next month when we will going to prepare the employees register i need the current employees name list excepted the resigned employees.
Please help me. Thank you!
Best Regards,
S.SIMBU.,
Chennai-Tamil Nadu-INDIA
Mynda Treacy says
Hi Silambara San.s,
You’ll need to send your file to the help desk so we can see exactly how it is laid out. Please also provide clear instructions to help us understand your needs.
Kind regards,
Mynda.
Duane Clark says
This was GREAT!!!! Been trying to figure out how to write a nested formula for thisl
Mynda Treacy says
Cheers, Duane 🙂
Kent Collins says
Very, very cool and perfect for a logistics solution that I am putting together.
However, I am not able to recreate the Excel array solution on a google spreadsheet. Do you have any idea why that would be? I get a a parse error.
Thank you
Carlo Estopia says
Hi Kent,
As much as we would want to shed some light on your problem,
We don’t have much expertise to our disposal regarding Google spreadsheets.
All I know is that not all of Excel’s functions will work with Google;hence,
It really depends on what funtions you’re using.
Cheers,
CarloE
John Keyes says
Now that Google has significantly changed their Sheets app, can you shed light now on how this can be done? I tried to do this in Google Sheets and the only stumbling block (after hitting ctrl-shift-enter) was that the MATCH parameter was not recognized: “Did not find value ‘1’ in MATCH evaluation.”
Thanks!
John
Mynda Treacy says
Hi John,
Sorry, I’m not familiar with Google Sheets. Only Excel 🙂
Kind regards,
Mynda
Christophe Vercarr says
Hey John,
I’m having the same problem as yours. Been looking for a solution the last couple of the days with little success so far. Have ultimately found a solution to this problem?
Regards
John Keyes says
Nothing yet Christophe
But I am monitoring it because it’s solving a number of problems with clients that only work with Google Docs. This is perhaps in the wrong forum…
If I find out more, I will come back…
owais says
nice sharng
Carlo Estopia says
Hi Owais,
Nice words. Thank you, on behalf of Mynda.
Cheers.
CarloE
mohit says
Hi Mynda,
Great code… works like a charm..
But I have one issue.. I am using VBA and the number is sheets which will have the data is not known in the beginning… so my query is
1. How do i create a named array at run time
2. how do i insert the formula in the cell using VBA
3. This formula gives rsults only if I insert it in the cell and press ctrl+shift+ enter in each cell individually… if i drag then i dnt get the results.
Awaiting your comments on the same
Carlo Estopia says
Hi Mohit,
1. To create a named array:
Horizontal
ActiveWorkbook.Names.Add Name:="RunTime", RefersToR1C1:= _ "={""jay"",""wash""}"or Vertical
ActiveWorkbook.Names.Add Name:="RunTime", RefersToR1C1:= _ "={""jay"";""wash""}"2. Code to put contants to array to a range (I presume you already know how to add a commandbutton because you said you’re using VBA). I’m saying this anyway: Add a commandbutton and copy&paste the code as applicable(Horizontal, Vertical)
how to add commandbutton
Horizontal
Dim s As Variant s = Evaluate(ThisWorkbook.Names("RunTime").RefersTo) Range("A1:B1").value = sor Vertical
Dim s As Variant s = Evaluate(ThisWorkbook.Names("RunTime").RefersTo) Range("A1:A2").value = s3. Regarding this… are you having problem with this one? lol.
I am not in the proper position to answer you this because I am
not one of the creators of Excel. To give you a hint however, named arrays
are not technically arrays but constants;hence, if you notice I used the Evaluate()
function to put it into an array. Think about the transpose function… you’ll get
the idea. And please, there’s nothing we can do about it.
Cheers.
Carlo
mohit says
Dear Carlo,
Many thanks for your inputs.
I got my doubts clarified by googling a bit.
Regarding point 2, guess my question was not clear.
What I want to know is,
If I insert it manually in a cell I will type
=VLOOKUP(A4,INDIRECT(“‘”&INDEX(SheetList,MATCH(1,–(COUNTIF(INDIRECT(“‘”&SheetList&”‘!$A$1:$c$4″),A4)>0),0))&”‘!$A$1:$c$4”),2,FALSE)
and press ctrl+shift+enter
But if I want to insert this through VBA I will have to insert the command
Range(“A1”).FormulaArray = “eqivalent of the VLOOKUP formula” and then loop this statement till the end of the sheet.
now what I want to know is what will be the eqivalent of this VLOOKUP formula???
Carlo Estopia says
Hi Mohit,
LOLz.I could hardly see that was your intended question. I thought you were relating 2 from 1.
Anyways…. so much of semantics….
Try this:
Range("A1").FormulaArray = _ "=VLOOKUP(R[-13]C[-3],INDIRECT(""'""&INDEX(SheetList,MATCH(1,–(COUNTIF(INDIRECT(""'""&SheetList&""'!$A$1:$C$4""),R[-13]C[-3])>0),0))&""'!$A$1:$c$4""),2,FALSE)" Range("A1").AutoFill Destination:=Range("D17:D21"), Type:=xlFillDefaultNote: This is the equivalent of your formula in this post :=VLOOKUP(A4,INDIRECT(“‘”&INDEX(SheetList,MATCH(1,–(COUNTIF(INDIRECT(“‘”&SheetList&”‘!$A$1:$c$4″),A4)>0),0))&”‘!$A$1:$c$4″),2,FALSE)
Cheers.
CarloE
PS: I really didn’t get to troubleshoot the formula because I don’t have the data that will perform the INDIRECT & Match function to return
the table array… hence it is returning an Error.But if you’ll put this all in a commandbutton, this will show the formula that you presented to me
in this post. If this will function in yours, then good. If not, you might as well send your file through HELP DESK. so we can give you the exact formula you need!!!
Francie says
I am trying to do vlookup and hlookup together. I need to get the exact figure for a column and row from another sheet, please help I am stuck. Thank you
Carlo Estopia says
Hi Francie,
Please try this formula:
Kobe the black mamba's stats last 5 games B C D E F G 2 Points Per Quarter 3 1 2 3 4 4Team Vs Utah 15 5 10 0 5 Phoenix 16 6 11 1 6 Chicago 17 7 12 2 7 OKC 18 8 13 3 8 Miami 19 9 14 4 Team Vs:Chicago (C11) Quarter: 3 (C12) Score(Formula):=VLOOKUP(C11,C4:G8,MATCH(C12,D3:G3)+1,FALSE) result:12The data is B2:G8 including the headers Points Per Quarter and Team Vs
The lookup values are in C11 and C12. You may just hardcode the lookupvalues like this
=VLOOKUP("Chicago",C4:G8,MATCH(3,D3:G3)+1,FALSE)Read More VLOOKUP’s
Cheers.
CarloE
Debbie says
Awesome Mynda, thank you very much.
Mynda Treacy says
You’re most welcome, Debbie 🙂
Adam says
HI Mynda
As always, how to make the hard and complicated formula’s look easy, with a step by step break down, I’ve been using excel since 1996, and still I find new tricks and tips (inspired by you blog)
Thank you
Regards
Adam
Mynda Treacy says
🙂 Wow, thanks Adam.
Lhey says
Hi Mynda, I can’t seem to make the formula work.
Can I send you my excel file so you may kindly check where I went wrong?
Please….
Thanks 🙂
Mynda Treacy says
Hi Lhey,
Sure, please send your file via the help desk.
Kind regards,
Mynda.
Lhey says
Hi Mynda,
It’s working now. Just made a mistake in the Name Range part.
Your formula is awesome!
Thanks,
Lhey
Mynda Treacy says
Great 🙂 Glad you got it working.
Mehul says
Hello Mynda, I have gone through the various tricks and techniques. The VLOOKUP for multiple sheets is an excellent piece and the way you have explained is terrific!!! I am advanced excel user but after going through your tricks, I really feel that you are Goddess of Excel in front of whom I am dwarf. Thanks dear for your tips on excel.
Mynda Treacy says
You’re welcome. Thanks for your kind words, Mehul 🙂
Philip says
Question- How could this formula be revised to allow for this scenario.
For example what if “Spiderman” was on multiple tabs. Only one tab would have a cost associated. For example sheet2 value 0, sheet3 value $28.00.
The formula; as is would return 0, ie returns the first match, but what I want is to return the $28 from sheet3
Mynda Treacy says
Hi Philip,
You can use the 3D SUMIF formula for this.
Kind regards,
Mynda.
Philip says
Thank you for your succinct solution
Mynda Treacy says
You’re welcome, Philip 🙂
Jerry Beaucaire says
If you’d like to take this one step further, on my site here where that same technique is demonstrated there is an additional application that uses that same technique to create a dynamic HYPERLINK in an adjacent cell that you can click to jump directly to the data on that specific sheet.
3D Vlookup & 3D Hyperlink – http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/search-functions/3d-lookup
Mynda Treacy says
Thanks for sharing, Jerry 🙂
tristan tran says
just looking at this gives me a headache 🙁
Mynda Treacy says
🙂 this is quite an advanced formula, Tristan. Maybe start off with a regular VLOOKUP first.
renjini says
The data was very useful, but the formula looks too lengthy.
Mynda Treacy says
Hi Renjini,
Thanks for your feedback. Have you got an alternative? Perhaps you’d like to share it.
Cheers,
Mynda.
r says
Countif is a function amazing … and here great use with indirect function … I had never seen it work so. Thanks Mynda, good trick!
Mynda Treacy says
Thanks, r. Glad you liked it 🙂
Tahir Rasheed says
Fantastic
Mynda Treacy says
Cheers, Tahir 🙂