 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
| |
See also:Dynamic Named Ranges | Vlookup |Hlookup Formula | Left Lookup in Excel | Excel Lookup Functions | Multi-Table Lookup |Dynamic Excel Lookups
Rather than bog you Spreadsheet down with hundreds, if not thousands of formulas, use a single formula with flexible and changeable Arguments. In this example I will use theINDEX/MATCH functions nested together. You can also instruct the end formula to return the corresponding cell, to the match, on the left or right. However, the the same principles can apply to most Excel formulas.
In this example I have used the range A2:D14 as my table range. I have also made good use ofNamed Ranges andData Validation. The single formula, in this case, ends being;
=IF(ISERROR(INDEX(DataTable,MATCH(Look_For,INDIRECT(Column_To_Look),0)+1,IF(Look_Left_Right="Right",MATCH(Column_To_Look,Heads,0)+Offset_To_Column,MATCH(Column_To_Look,Heads,0)-Offset_To_Column))),"Invalid Criteria",INDEX(DataTable,MATCH(Look_For,INDIRECT(Column_To_Look),0)+1,IF(Look_Left_Right="Right",MATCH(Column_To_Look,Heads,0)+Offset_To_Column,MATCH(Column_To_Look,Heads,0)-Offset_To_Column)))
Or, if don't mind seeformula errors IF invalid argument criteria is used, it is simply;
=INDEX(DataTable,MATCH(Look_For,INDIRECT(Column_To_Look),0)+1,IF(Look_Left_Right="Right",MATCH(Column_To_Look,Heads,0)+Offset_To_Column,MATCH(Column_To_Look,Heads,0)-Offset_To_Column))
<< Back to Excel Formulas Index << Back to Excel Named Ranges
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
FREE Excel Help