Vlookup across multiple sheets
This article demonstrates an array formula that searches two tables on two different sheets and returns multiple results. Sheet1 contains table1 and sheet2 contains table 2.
The search value is Pen and is in cell B9, the formula finds two matches in sheet 1 row 3 and 6. It then continues to sheet 2 and finds two matches, row 3 and 6. The adjacent values from each match is returned to cell range C9 in sheet 1.
Array Formula in cell C9:
How to create an array formula
- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.
How to copy an array formula
- Select cell C9
- Copy (Ctrl + c)
- Select cell range C9:C13
- Paste (Ctrl + v)
Named Range
tbl_1 - Sheet1!B3:C7
tbl_2 - Sheet2!B3:C7
How to create named range
- Select cell range Sheet1!B3:C7
- Type tbl_1 in name box
Explaining array formula
Step 1 - What values are equal to criterion?
The equal sign lets you create a logical expression that compares cell value in B9 with values in cell range B3:B7, it creates an array containing boolean values. TRUE or FALSE.
$B$9=$B$3:$B$7
becomes
"Pen"={"Pen"; "Eraser"; "Paper"; "Pen"; "Paper clip"}
and returns
{TRUE; FALSE; FALSE; TRUE; FALSE}
Step 2 - Convert array to row numbers
The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).
If the logical expression returns TRUE the IF function replaces those values with the corresponding row numbers, if FALSE it returns "" (blank).
IF(($B$9=$B$3:$B$7), ROW($B$3:$B$7)-MIN(ROW($B$3:$B$7))+1, "")
becomes
IF({TRUE; FALSE; FALSE; TRUE; FALSE}, ROW($B$3:$B$7)-MIN(ROW($B$3:$B$7))+1, "")
becomes
IF({TRUE; FALSE; FALSE; TRUE; FALSE}, {3; 4; 5; 6; 7}-MIN({3; 4; 5; 6; 7})+1, "")
becomes
IF({TRUE; FALSE; FALSE; TRUE; FALSE}, {3; 4; 5; 6; 7}-3+1, "")
becomes
IF({TRUE; FALSE; FALSE; TRUE; FALSE}, {1; 2; 3; 4; 5}, "")
and returns {1; ""; ""; 4; ""}
Step 3 - Return the k-th smallest row number
To be able to return a new value in a cell each I use the SMALL function to filter column numbers from smallest to largest. The SMALL function ignores text and blank values in the array which is very handy in this case.
SMALL(IF(($B$9=$B$3:$B$7), ROW($B$3:$B$7)-MIN(ROW($B$3:$B$7))+1, ""), ROW(A1))
becomes
SMALL({1; ""; ""; 4; ""}, ROW(A1))
becomes
SMALL({1; ""; ""; 4; ""}, 1)
and returns 1.
Step 4 -Â Return a value or reference of the cell at the intersection of a particular row and column
The INDEX function returns a value based on a cell reference and a row number and a column number if needed.
INDEX(tbl_1, SMALL(IF(($B$9=$B$3:$B$7), ROW($B$3:$B$7)-MIN(ROW($B$3:$B$7))+1, ""), ROW(A1)), 2)
becomes
INDEX(tbl_1,1, 2)
becomes
INDEX({"Pen", 1,5; "Eraser", 2; "Paper", 1,7; "Pen", 1,7; "Paper clip", 3},1, 2)
and returns $1,5
Step 5 - Return another value if expression is an error
The IFERROR function returns value_if_error if expression is an error and the value of the expression itself otherwise
IFERROR(value, value_if_error)
IFERROR function traps errors and starts looking for values in tbl_2
Lookup across multiple sheets Add-In
Lookup across multiple sheets is an add-in for for Excel 2007/2010/2013 (not Mac!) that lets you lookup a value or multiple values and return multiple values or rows from multiple sheets.
Features
- Easy-to-use custom function
- Lookups across multiple sheets
- You can use multiple search values at the same time
- Use wildcards to refine your searches even further
- Up to 127 multiple ranges or sheets
- Returns all values or rows
- Returns unique distinct values or rows
- Returns duplicate values or rows
What you get
- Lookup across multiple sheets Add-in for Excel 2007/2010/2013 *.xlam file.
- Lookup across multiple sheets Add-in for Excel 2003 *.xla file.
- Instructions on how to install.
- Instructions on how to use custom function.
- Excel *.xlsx example file.
- Excel 2003 *.xls example file.
- 2 licenses, home and office computer.
- You can buy VBA source file for $10 more.
Examples
Example 1 - Lookup one value in two sheets and return multiple rows
Example 2 - Lookup one value in two sheets and return multiple unique distinct rows
Example 3 - Lookup one value in two sheets and return multiple duplicate rows
Example 4 - Lookup two values in two sheets and return all matching rows
Example 5 - Lookup two values in two sheets and return unique distinct rows
Example 6 - Lookup two values in two sheets and return duplicate rows
Example 7 - Lookup two values using wildcards in two sheets and return all matching rows
Example 8 - Lookup two values using wildcards in two sheets and return multiple unique distinct rows
Example 9 - Lookup two values in two sheets using wildcards and return multiple duplicate rows
Example 10 - Wildcard examples
How to use the custom function
Purchase Lookup across multiple sheets Add-In (Excel 2003/2007/2010/2013)Â - Price $19 USD
Questions:
In all your examples above you search for a value in the first column. Can the add-in search other columns?
Yes, of course! You choose which column to search, press with left mouse button on and read How to use the custom function above.
What are unique distinct values?
All values but duplicate values are removed.
Can I search case sensitive?
No, it is case insensitive.
How do I enter this user defined function?
It is an array formula.
-
- Type user defined function in formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.
Is there a money back guarantee?
Sure, you have un-conditional money back guarantee for 14 days.
Can I view the vba source code?
No, it is locked for viewing but you can buy VBA source file for $40 more.
I have more questions?
Use this contact form to let me know.
The add-in works perfectly and it definitely saved me lots of time and effort. Thank you for answering my questions.
Add in category
Table of Contents Split data across multiple sheets - VBA Add values to worksheets based on a condition - VBA […]
Merge Ranges is an add-in for Excel that lets you easily merge multiple ranges into one master sheet. The Master […]
Table of Contents How to save custom functions and macros to an Add-In How to add a custom-made item to […]
Vlookup and return multiple values category
This post explains how to lookup a value and return multiple values. No array formula required.
VLOOKUP a multi-column range and return multiple values.
I will in this article demonstrate how to use a value from a drop-down list and use it to do […]
Excel categories
27 Responses to “Vlookup across multiple sheets”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Please forgive my limited knowledge, but is an "add-in" the same as a "module"? This seems like the solution I might need to purchase and put into play, but all of my data is on a single worksheet and performance is significantly slowing. I believe that performance could be improved by an add-in or module that could be "accessed" through a message box? Thanks for any advice or assistance!! The distension of the vein in my forehead means that I've just reached certain limits in trying to solve these issues on my own!
Jim,
No, you don´t need to buy this addin.
I will soon add a custom function to this blog post: How to return multiple values using vlookup in excel
Jim,
I have now added a custom function!
https://www.get-digital-help.com/how-to-return-multiple-values-using-vlookup-in-excel/#vba
Hi Oscar,
It's really amazing!!! And you explain it so well!
I have searched for it so much.
Thanks a lot!!!!!
But still I have a question:
I would like that if there is nothing more to find, the formula will return a blank cell and not "#NUM!".
Is it possible?!
Noa,
Excel 2007
Oscar,
I am trying to do a vlookup between multiple worksheets but there are a few duplicates with the same value. Do I need to purchase the addin if I want it to pull back the duplicates?
Penny,
You can use the example in this post if you have 2 sheets. It will also get duplicate values.
The addin returns all values, unique distinct values and duplicate values from two or more sheets. Press with left mouse button on the examples above.
You can use this contact form if you have more questions.
Oscar,
Thanks for your answer!!
Hi Oscar,
I have a problem with lookups in different sheet. In first sheet, in first column I have names of people. In second column i have dates. There are more sheets in the workbook named by dates containing names of people and amounts. Now in third column i have to get amounts by looking up the names from first column in the specific date sheet depending on the date mentioned in second column. I tried using indirect function in vlookup but it does not work. Can you help me with this problem. Thanks.
Hi,
I need to return a value by comparing more than 5 cells in 2 sheets. pls help me.
I have seen your vba and your posted solution above. The formula will look in table_2 when no more values will be found in table_1, (IFERROR)correct?
How would you modify the formula (or a VBA) to accept more than 2 tables? Let's consider a database of spare parts with 20 shops national wide, all have the same database format, Ctrl-F allows the search (as long as combined in the same book) but a formula would be an interesting approach.
Thanks to give your opinion.
Cyril,
The array formula becomes really large and complicated if I try add more tables.
no problem, just being curious, I end up with similar concern, formula becomes expensive.
If I find a vba I'll let you know.
cyril,
I have seen your vba and your posted solution above
Did you buy the Lookup across multiple sheets Add-In?
Our computer pool is mostly Mac running excel 2011, although VBA compatible, I am not sure that this code will run as is.
Made a code and posted it on VBA excel, so far no one able to assist me. Sadly few guru are specialized mac. Hence my hesitation even for a few bucks.
What do you reckon on this?
Re vba I was referring to your:
https://www.get-digital-help.com/how-to-return-multiple-values-using-vlookup-in-excel/#vba
Cyril,
I don´t know if the add-in works on a mac.
=IF(ISERROR(VLOOKUP(C6,INPUT!C5:D21,2,0)),VLOOKUP(lookup value,array,2,0)),VLOOKUP(ISERROR!C6,INPUT!C5:D21,2,0))
Hi SURESH, what would that be for?
PS reduced to =IFERROR(VLOOKUP(C6,INPUT!C5:D21,2,0),VLOOKUP(lookup value,array,2,0)) would work the same with faster calculation.
pleas sent me all formula of Excel 2007 free
Hi Oscar,
Using the formula, How to vlookup more than 2 sheets?
Can you assist me please?
Thank you
Sagit,
Using the formula, How to vlookup more than 2 sheets?
You can´t, but this add-in can:
Lookup across multiple sheets Add-In
This looks almost like what I need.
I have a vehicle maintenance workbook containing 17 employees' fuel, maintenance and other information.
What I need to do is find a way to get the fuel and maintenance totals for each of them, but they are not all in the same department/division, so I can't use the 'easy' 3-D reference way to total monthly vehicle expenses. I have to present totals for each department/division, but NOT move the sheets out of their alphabetic tab-order.
Hi, I'm looking for a way to efficiently (200 000 rows) extract a subset of columns from one table based on selection from a different table.
something like a one-to-many relationship.
I was wondering if your Vlookup across multiple sheets in excel macro would do that?
Thanks
reculard,
I think you are looking for something like this:
https://www.get-digital-help.com/2012/10/10/lookups-in-a-related-table-array-formula/
Hi , I am working on Excel for past couple of weeks and i need to know,how to display the values from sheet1 and sheet 2 to sheet 3
for eg: In sheet 1 contains 100 values and sheet contains the same 100 values if the values are same in both sheet 1 and 2 then in Sheet 3 the values which are same should be displayed in sheet 3.