 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
| |
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.
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. ALLpurchases 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
Excel Trader PackageTechnical 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
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates