Forum Discussion
Nested IF AND formula for a table of values
To make your NESTED IF statement less complicated and more dynamic, it is best to unpivot your data and maintain one single table reference (as long as the same parameters/information are shared). Here's an example of what I mean:
With this new setup, your complex NESTED IF statement is no longer needed and a simple SUMIFS statement can be used to return the right Fee figure you're looking for
Normalising the data into a table has a lot to commend it, but it is possible to work directly with data presented as arrays.
= LET(
FeeTable, IF(Binsize=35, Size35fees, Size64fees),
Freq, XMATCH(Frequency, FrequencyOptions),
Fee, INDEX(FeeTable, Freq, Quantity),
Fee)
or, in older versions of Excel
= INDEX(
IF(Binsize=35, Size35fees, Size64fees),
MATCH(Frequency, FrequencyOptions, 0),
Quantity)
PeterBartholomew1 Thanks, Peter. I like the drop down menu feature. I'll play around with both and come up with something that works well for our purposes.
Much appreciated and have a great New Year.
-Nick
