Dynamic Excel Formulas

<p class="j"><b>See also:</b> <a href="http://www.ozgrid.com/Excel/DynamicRanges.htm">Dynamic Named Ranges</a> | <a href="http://www.ozgrid.com/Excel/excel-vlookup-formula.htm">Vlookup</a> | <a href="http://www.ozgrid.com/Excel/excel-hlookup-formula.htm">Hlookup Formula</a> | <a href="http://www.ozgrid.com/Excel/left-lookup.htm">Left Lookup in Excel </a>| <a href="http://www.ozgrid.com/Excel/lookup-functions.htm">Excel Lookup Functions</a> | <a href="http://www.ozgrid.com/Excel/multi-table-lookup.htm">Multi-Table Lookup</a> | <a href="http://www.ozgrid.com/Excel/dynamic-lookups.htm">Dynamic Excel Lookups</a></p><p class="c"><b><a href="http://www.ozgrid.com/charts/dashboard-reports.htm">Excel Dashboard Reports &amp; Excel Dashboard Charts</a> 50% Off</b></p><h2>Excel Dynamic Formulas</h2><p class="j">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 <a href="http://www.ozgrid.com/Excel/left-lookup.htm">INDEX/MATCH functions</a> nested together. You can also instruct the end formula to return the corresponding cell, to the match, on the left or right.&nbsp; However, the the same principles can apply to most <a href="http://www.ozgrid.com/Excel/">Excel formulas</a>.</p><p class="j">In this example I have used the range A2:D14 as my table range. I have also made good use of <a href="http://www.ozgrid.com/Excel/named-ranges.htm">Named Ranges</a> and <a href="http://www.ozgrid.com/Excel/data-validation.htm">Data Validation</a>. The single formula, in this case, ends being;</p><p class="l">=IF(ISERROR(INDEX(DataTable,MATCH(Look_For,INDIRECT(Column_To_Look),0)+1,IF(Look_Left_Right=&quot;Right&quot;,MATCH(Column_To_Look,Heads,0)+Offset_To_Column,MATCH(Column_To_Look,Heads,0)-Offset_To_Column))),&quot;Invalid Criteria&quot;,INDEX(DataTable,MATCH(Look_For,INDIRECT(Column_To_Look),0)+1,IF(Look_Left_Right=&quot;Right&quot;,MATCH(Column_To_Look,Heads,0)+Offset_To_Column,MATCH(Column_To_Look,Heads,0)-Offset_To_Column)))</p><p class="l"><b>Or</b>, if don't mind see <a href="http://www.ozgrid.com/Excel/formula-errors.htm">formula errors</a> IF invalid argument criteria is used, it is simply;</p><p class="l">=INDEX(DataTable,MATCH(Look_For,INDIRECT(Column_To_Look),0)+1,IF(Look_Left_Right=&quot;Right&quot;,MATCH(Column_To_Look,Heads,0)+Offset_To_Column,MATCH(Column_To_Look,Heads,0)-Offset_To_Column))</p><h3><a href="http://www.ozgrid.com/Excel/dynamic-formulas.zip">Download Example</a></h3><p class="j">&lt;&lt; <b>Back to</b> <a href="http://www.ozgrid.com/Excel/default.htm">Excel Formulas</a> Index <b>&lt;&lt; Back to</b> <a href="http://www.ozgrid.com/Excel/named-ranges.htm">Excel Named Ranges</a> </p>