There are numerous ways of rewriting convoluted nested IF()
s, only 8 of which are covered here to accommodate versions of Excel going back to 2000.
Some rewrites use just 1 function. VLOOKUP()
is the best-known of the single functions used for rewriting nested IF()
s. SUMPRODUCT()
, however, is the most powerful due to its number-crunching prowess. In the case of Rewrite #8 (REPT()
), the function is used repeatedly.
Some rewrites use 2-function formulas. Among these are the more commmonly used INDEX-MATCH and the lesser known VLOOKUP-CHOOSE. While INDEX-MATCH is preferred because it avoids a limitation of VLOOKUP()
, VLOOKUP-CHOOSE lets VLOOKUP()
“look to the left,” and therefore overcome that limitation.
If you can believe it, one nested IF()
rewrite uses zero functions: Boolean Logic (Rewrite #7). Instead, this rewrite uses a series of comparisons, each producing a 1 or a 0, to be multiplied by corresponding values in order to produce the final answer. Rewrite #8 (REPT()
) closely follows this format.
Below are the nested IF()
rewrites explained earlier (plus the nested IF()
which inspired them):
Nested IF()
s
=IF(B3=$G$3, $H$3, IF(B3=$G$4, $H$4, IF(B3=$G$5, $H$5, IF(B3=$G$6, $H$6, IF(B3=$G$7, $H$7, IF(B3=$G$8, $H$8, IF(B3=$G$9, $H$9, IF(B3=$G$10, $H$10)))))))) |
---|
Situations like this one are why nested IF() rewrites are needed. |
Nested IF()
s using ZAR (South African Rand)
Rewrite #1 — VLOOKUP()
=VLOOKUP(B4, $G$3:$H$10, 2, FALSE) |
---|
One function is used. |
Rewrite #1 using GBP (UK Pound Sterling)
Rewrite #2 — CHOOSE-MATCH
=CHOOSE(MATCH(B5, $G$3:$G$10, FALSE), $H$3, $H$4, $H$5, $H$6, $H$7, $H$8, $H$9, $H$10) |
---|
Two-function formula is used. |
Rewrite #2 using CAD (Canadian Dollar)
Rewrite #3 — INDEX-MATCH
=INDEX($H$3:$H$10, MATCH(B6, $G$3:$G$10, FALSE)) |
---|
Two-function formula is used. In this case, this is the most common for nested IF() rewrites. |
Rewrite #3 using EUR (Euro)
Rewrite #4 — VLOOKUP-CHOOSE
=VLOOKUP($B$7, CHOOSE({1,2}, $I$3:$I$10, $H$3:$H$10), 2, FALSE) |
---|
Two-function formula is used. Not as common as INDEX-MATCH, this rewrite shows how VLOOKUP can overcome its best-known limitation. |
Rewrite #4 using CHF (Swiss Franc)
Rewrite #5 — SUMPRODUCT()
=SUMPRODUCT(--(B8=$G$3:$G$10), $H$3:$H$10) |
---|
One function is used. When used with numeric data, SUMPRODUCT() is the most powerful way to rewrite a nested IF() |
Rewrite #5 using AUD (Australian Dollar)
Rewrite #6 — SUMIF()
=SUMIF($G$3:$G$10, B9, $H$3:$H$10) |
---|
One function is used. SUMIF() is considered an enhanced SUM() function. |
Rewrite #6 using XCD (East Caribbean States Dollar)
Rewrite #7 — Boolean Logic
=(B10=$G$3)$H$3 + (B10=$G$4)$H$4 + (B10=$G$5)$H$5 + (B10=$G$6)$H$6 + (B10=$G$7)$H$7 + (B10=$G$8)$H$8 + (B10=$G$9)$H$9 + (B10=$G$10)$H$10 |
---|
Zero functions are used. Instead, a series of comparisons are made and muliplied by numeric values to obtain the final answer. |
Rewrite #7 using INR (Indian Rupee)
Rewrite #8 —REPT()
=1 * ( REPT($H$3,(B11=$G$3)) & REPT($H$4,(B11=$G$4)) & REPT($H$5,(B11=$G$5)) & REPT($H$6,(B11=$G$6)) & REPT($H$7,(B11=$G$7)) & REPT($H$8,(B11=$G$8)) & REPT($H$9,(B11=$G$9)) & REPT($H$10,(B11=$G$10)) ) |
---|
One function is used– repeatedly, in a way similar to Boolean Logic. Since REPT() is normally used with text, the final output from this series of REPT() calls needs to be multiplied by 1 for use as a number later. |
Rewrite #8 using GBP (UK Pound Sterling)
Certain nested IF()
rewrites are better-suited for certain situations (especially if text is involved). While certain nested IF()
rewrites are preferred over others, all nested IF()
rewrites are worth knowing.
This excerpt is taken from 8 Ways To Rewrite nested IF()
Functions at Magna Carta XLS Communications. Each of the 8 nested IF()
rewrites from that post will be featured in its own post here.
Other posts will be migrated to this blog before I begin writing posts natively here.