Javascript DHTML Drop Down Menu Powered by dhtml-menu-builder.com

Excel Dynamic Formulas. Flexible & Changeable Formula Arguments

Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL | More Free Downloads.. Best Value: Finance Templates Bundle

See also: Dynamic Named Ranges | Vlookup | Hlookup Formula | Left Lookup in Excel | Excel Lookup Functions | Multi-Table Lookup | Dynamic Excel Lookups

Excel Dynamic Formulas

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 the INDEX/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 of Named Ranges and Data 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 see formula 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))

Download Example

<< Back to Excel Formulas Index << Back to Excel Named Ranges

New & Less Than You Think: List Managers | Working With Excel Sheets In VBA | Excel Charting Lessons | Delete rows by condition | TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL | More Free Downloads.. Best Value: Finance Templates Bundle

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 special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out: Analyzer XL | Downloader XL | Smart VBA | Trader XL Pro (best value) | ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

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

GIVE YOURSELF OR YOUR COMPANY 24/7 MICROSOFT EXCEL SUPPORT & QUESTIONS

FREE Excel Help