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:
The following formula is an Excel 365 formula:
It spills values to cell C9 and cells below as far as needed. This Excel 365 formula is much smaller than the one for earlier versions, this shows clearly how far Excel has come the last years. Here is a breakdown:
VSTACK: This function stacks two or more arrays vertically. In this case, it's used twice.
 VSTACK(C3:C7, Sheet2!C3:C7): This stacks the values in cells C3:C7 from the current sheet (Sheet1) with the values in cells C3:C7 from Sheet2.
 VSTACK(B3:B7, Sheet2!B3:B7): This stacks the values in cells B3:B7 from the current sheet (Sheet1) with the values in cells B3:B7 from Sheet2.
 FILTER: This function filters the data based on a condition. In this case, the condition is:
 VSTACK(B3:B7, Sheet2!B3:B7) = Sheet1!B9
This means that the formula will only return the values from the stacked arrays where the value in the first stacked array (VSTACK(B3:B7, Sheet2!B3:B7)) matches the value in cell B9 on Sheet1. So, the entire formula can be read as:
"Filter the stacked values from columns C and B (from both Sheet1 and Sheet2) where the value in the stacked column B matches the value in cell B9 on Sheet1, and return the corresponding values from the stacked column C." In other words, the formula is looking for matches between the values in column B (across both sheets) and the value in cell B9, and then returning the corresponding values from column C.
Note that this formula is using the new dynamic array formulas in Excel 365, which allows for more flexible and powerful data manipulation. The FILTER function is one of the new functions introduced in Excel 365, and it's used here to filter the data based on the condition specified.
The following instructions are for earlier Excel versions than Excel 365.
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)
Explaining array formula (earlier Excel versions)
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 kth 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 AddIn
Lookup across multiple sheets is an addin 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
 Easytouse 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 Addin for Excel 2007/2010/2013 *.xlam file.
 Lookup across multiple sheets Addin 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
Questions:
In all your examples above you search for a value in the first column. Can the addin 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 unconditional 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 addin 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 addin 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 AddIn How to add a custommade 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 multicolumn range and return multiple values.
I will in this article demonstrate how to use a value from a dropdown 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.
Contact Oscar
You can contact me through this contact form
Please forgive my limited knowledge, but is an "addin" 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 addin 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.getdigitalhelp.com/howtoreturnmultiplevaluesusingvlookupinexcel/#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, CtrlF 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 AddIn?
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.getdigitalhelp.com/howtoreturnmultiplevaluesusingvlookupinexcel/#vba
Cyril,
I don´t know if the addin 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 addin can:
Lookup across multiple sheets AddIn
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' 3D 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 taborder.
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 onetomany 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.getdigitalhelp.com/2012/10/10/lookupsinarelatedtablearrayformula/
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.