# Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

## Go to Excel Lookup Formula Result

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only \$145.00. \$59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help

Excel is quite rich in Lookup type formulas, some of the more popular ones are VLOOKUP , INDEX/MATCH and HLOOKUP . These all do a great job in looking up a value we specify and then return a corresponding result. However, it's often the case that we need to go to the row containing the found value, or its offset return.

What we can do to actually go to our found result is nest the CELL function with some Text Function nested inside the Hyperlink Function .

Lets use an example table as shown below. The upper left cell is A1 and only the 1st 10 rows and 3 columns are shown to keep it simple.

 Names Age City Dave 21 New York Bill 33 Washington Frank 54 Dallas Mary 19 San Francisco John 50 Utah Harry 54 Kansas June 30 Ohio Kate 60 California Aleisha 55 Montana

What we are doing is looking up a name from Column A and returning any corresponding column from the found row in Column A. Before we get to the lookup part though we need to return the Workbook and Worksheet name in the format needed for the Hyperlink function. For example [Book1.xls]Sheet1!

As mentioned above, we use the CELL Function with some Text Functions to parse out only the bits we need. As the CELL Function is a Volatile Function it pays to create only 1 instance of the needed Formula and re-use as many times as needed. A Named Formula is ideal for this.

1. Activate the Worksheet the data table resides on.
2. Go to Insert>Name>Define or Formulas>Name Manager - New in 2007.
3. Use the name WbSheet and have it refer to: =MID(CELL("filename",\$A\$1),FIND("[",CELL("filename",\$A\$1)),256)&"!"
4. OK that and back to Excel.

You can see what this will do by simply typing =WbSheet into any cell.

As we are going to be looking up a name from our data table it would also pay to create a Validation list based off the names in Column A (optional). Let's say the name will be chosen from, or typed in K1.

In any spare cell Enter the formula as shown below

Note the underlined 1 this what determines the relative Column in our table to which we go when clicking the Hyperlink. This can of course also be variable by simply entering the number in another cell, that is

Or, if the column order may change, we can again (optional) use a Validation list to specify the Column heading and then use the MATCH function to return its relative position. In the case below I have again used \$K\$2 to return the column heading needed.