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

1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
2. Press and hold Ctrl + Shift.
3. Press Enter once.
4. Release all keys.

How to copy an array formula

1. Select cell C9
2. Copy (Ctrl + c)
3. Select cell range C9:C13
4. Paste (Ctrl + v)

Named Range

tbl_1 - Sheet1!B3:C7
tbl_2 - Sheet2!B3:C7

How to create named range

1. Select cell range Sheet1!B3:C7
2. 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 Add-In is a custom function that let´s 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

Examples

Click on examples to expand.

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

Click on examples to expand.

What you get

• Lookup across multiple sheets Add-in for Excel 2007 *.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 2007 *.xlsx example file.
• Excel 2003 *.xls example file.
• 2 licenses, home and office computer.
• You can buy VBA source file for $10 more.