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:

=IFERROR(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), INDEX(tbl_2, SMALL(IF(($B$9=Sheet2!$B$3:$B$7), ROW(Sheet2!$B$3:$B$7)-MIN(ROW(Sheet2!$B$3:$B$7))+1, ""), ROW(A1)-SUM(--($B$9=$B$3:$B$7))), 2))

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.


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.

Lookup multiple sheets and return duplicate rows


Example 4 - Lookup two values in two sheets and return all matching rows.

lookup values across multiple sheets


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.

Questions:

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.

    1. Type user defined function in formula bar.
    2. Press and hold Ctrl + Shift.
    3. Press Enter once.
    4. 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 $10 more.

I have more questions?

Use this contact form to let me know.

Purchase Lookup across multiple sheets Add-In - Price $19 US

Add to CartView Cart

 

Related posts:

How to return multiple values using vlookup in excel

Vlookup – Return multiple unique distinct values in excel

Vlookup with 2 or more lookup criteria and return multiple matches in excel

Vlookup with multiple matches returns a different value in excel

Split data across multiple sheets in excel (vba)