Search Functions‎ > ‎

3D VLOOKUP

A normal VLOOKUP() searches a single table for data in the first column and returns a value from the matched row.  If the search needs to occur across multiple sheets, it is typical to use custom VBA or UDFs to make that possible. How do you accomplish this without the aid of VBA?

This technique uses an array formula to search a specified group of sheets the same way and return the answer from the first sheet where the match occurs.

=VLOOKUP(A3, INDIRECT("'" & INDEX(MySheets, MATCH(1, COUNTIF(INDIRECT("'" & MySheets &"'!A1:A50"), A3), 0)) & "'!A:B"), 2, 0)

SETUP:

1) Create a named range listing all the sheets to include in the 3D range. They are searched in the order listed, so that may be relevant.

 H 1 to search 2 Sheet1 3 Sheet2 4 Sheet3 5 Sheet4 6 Sheet5

For this exercise, the yellow cells are named MySheets.

2) Each sheet with data similar to this layout:

Sheet2
 A B 1 Name Hours 2 Name13 5 3 Name14 10 4 Name15 15 5 Name16 20 6 Name17 25 7 Sammy Sue 30 8 Name19 35 9 Name20 40 10 Name21 45 11 Name22 50 12 Name23 55 13 Name24 60

3) The Summary sheet lists the names you want in column A, then the function in column B.  There is a second formula in column C that shows the smaller section of formula that determines the sheet name.

Summary
 A B C 2 Name Hours Sheet 3 John Doe 15 Sheet3 4 Billy Bob 35 Sheet5 5 Sammy Sue 30 Sheet2 6 Name32 40 Sheet3 7 Name2 10 Sheet1 8 Name15 15 Sheet2

 Cell Formula B3 {=VLOOKUP(A3, INDIRECT("'" & INDEX(MySheets, MATCH(1, COUNTIF(INDIRECT("'" & MySheets &"'!A1:A50"), A3), 0)) & "'!A:B"), 2, 0)} C3 {=INDEX(MySheets, MATCH(1, COUNTIF(INDIRECT("'" & MySheets & "'!A1:A50"), A3), 0))}
Formula Array:   Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

4) 3D - HYPERLINK - since we've got the 3D VLOOKUP working, we can actually use that information to create a "clickable" hyperlink that will jump you directly to the location where the lookup formula made the match.  Like so:

Summary
 A B C D 2 Name Hours Sheet Hyperlink 3 John Doe 15 Sheet3 Link 4 Billy Bob 35 Sheet5 Link 5 Sammy Sue 30 Sheet2 Link 6 Name32 40 Sheet3 Link 7 Name2 10 Sheet1 Link 8 Name15 15 Sheet2 Link