## Vlookup across multiple sheets in excel

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 k-th 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 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

*Click on examples to expand.*

**one**value in two sheets and return multiple rows.

**one**value in two sheets and return multiple

**unique distinct**rows.

**one**value in two sheets and return multiple

**duplicate**rows.

**two**values in two sheets and return all matching rows.

**two**values in two sheets and return

**unique distinct**rows.

**two**values in two sheets and return

**duplicate**rows.

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

**two**values in two sheets using wildcards and return multiple

**duplicate**rows.

*Click on examples to expand.*

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

**Testimonials**

*The add-in works perfectly and it definitely saved me lots of time and effort. Thank you for answering my questions.*

### 27 Responses to “Vlookup across multiple sheets in excel”

### Leave a Reply

**How to add vba code to your comment:**

[vb 1="vbnet" language=","]

your code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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!

http://www.get-digital-help.com/2009/10/25/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 2007array_formula, "") + CTRL + SHift + ENTEROscar,

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, 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 aboveDid 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:

http://www.get-digital-help.com/2009/10/25/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:

http://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.