Vlookup across multiple sheets
This blog post describes how to search two tables on two sheets and return multiple results.
Sheet1 contains table1 and sheet2 contains table 2. The search value is Pen and is in cell B9.
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?
$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
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
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
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
IFERROR(value;value_if_error)
Returns value_if_error if expression is an error and the value of the expression itself otherwise
IFERROR function traps errors and starts looking for values in tbl_2
Download excel file
Vlookup across multiple sheets.xlsx
(Excel 2007 Workbook *.xlsx)
Recommended blog articles
Check out these posts and learn more about vlookup.
 How to return multiple values using vlookup
 Match two criteria and return multiple rows in excel
 Vlookup with 2 or more lookup criteria and return multiple matches in excel
 Using array formula to look up multiple values in a list
 Search for multiple text strings in multiple cells in excel
 Vlookup – Return multiple unique distinct values in excel
 Fuzzy vlookup (excel array formula)
 Lookup using two criteria in excel
 Search case sensitive and return multiple values in excel
 Vlookup a range in excel
 Vlookup with multiple matches returns a different value in excel
 Vlookup of three columns to pull a single record
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, click 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.
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
5 easy ways to VLOOKUP and return multiple values
This post explains how to lookup a value and return multiple values. No array formula required.
Vlookup with 2 or more lookup criteria and return multiple matches
VLOOKUP and return multiple matches based on many criteria.
Lookup values in a range using two or more criteria and return multiple matches in excel, part 2
In this post we are going to extract multiple text values. We are looking for names and the criteria are […]
Lookup and return multiple values on the same row from a range excluding blanks
Jim asks: I downloaded the file lookupvba3. I think I can use this to help me populate a calendar.I substituted […]
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
Use the img tag, like this: <img src="Insert pic link here">
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/2009/10/25/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. Click 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/2009/10/25/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.