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.
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.
For this exercise, the yellow cells are named MySheets.
2) Each sheet with data similar to this layout:
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.
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: