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

Hyperlink To Lookup Result

| | Information Helpful? Why Not Donate.

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

Lookup Formulas See Also: Lookup & Return Corresponding Result

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.

Hyperlink To Found Result

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.

Activate the Worksheet the data table resides on.
Go to Insert>Name>Define or Formulas>Name Manager - New in 2007.
Use the name WbSheet and have it refer to: =MID(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1)),256)&"!"
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

=HYPERLINK(WbSheet & ADDRESS(MATCH($K$1,$A$1:$A$2000,0),1),$K$1 & "s' Info")

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

=HYPERLINK(WbSheet & ADDRESS(MATCH($K$1,$A$1:$A$2000,0),$K$2),$K$1 & "s' Info")

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.

=HYPERLINK(WbSheet & ADDRESS(MATCH($K$1,$A$1:$A$2000,0),MATCH($K$2,$A$1:$J$1,0)),$K$1 & "s' Info")

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

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

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft