Search Functions‎ > ‎

3D VLOOKUP

Sample sheet:    3D-VLOOKUP & HYPERLINK.xls

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

Spreadsheet Formulas
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
 ABCD
2NameHoursSheetHyperlink
3John Doe15Sheet3Link
4Billy Bob35Sheet5Link
5Sammy Sue30Sheet2Link
6Name3240Sheet3Link
7Name210Sheet1Link
8Name1515Sheet2Link

Spreadsheet Formulas
CellFormula
D3=IF($C3=0,"",HYPERLINK("#"&CELL("address",INDEX(INDIRECT(C3&"!B:B"),MATCH(A3,INDIRECT(C3&"!A:A"),0))), "Link"))


Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online!
Comments