Limit to number of nested "IF" functions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've noticed that if I have too many IF's in one function, Excel 2000 refuses to calculate

Example
IN CELL N2 ... =IF(C2="HK","JK",IF(C2="CN","MF",IF(C2="TR","MF",IF(C2="IT","DWS",IF(C2="PH","DWS",IF(C2="TH","DWS",IF(C2="VN","CB",IF(C2="GB","CB",IF(C2="TW","CB",IF(C2="LK","CB",IF(C2="SP","CB","SH"))))))))))

Around the 7th or 8th IF is where it stops. Is there any way around this
 
Hi Michael,

7 is the maximum of nested IFs. One possible solution:
- Create a separate lookup table with the possible values of C2 and
their corresponding result values in two different columns)
- Use VLOOKUP to get your value. e.g. =VLOOKUP(C2,E1:F30,2,FALSE)
assuming that the columns E and F store your lookup table

Frank
 
Hi Michael!

This isn't a typical VLOOKUP of text because you have a default of SH
and perhaps you need to cover C2=""

In R1:S11 I have:

HK JK
CN MF
TR MF
IT DWS
PH DWS
PH DWS
TH DWS
VN CB
GB CB
TW CB
LK CB
SP CB

Then in N2 I have:

=IF(C2="","",IF(ISNA(VLOOKUP(C2,$R$1:$S$11,2,FALSE)),"SH",VLOOKUP(C2,$
R$1:$S$11,2,FALSE)))

However, you wouldn't have hit the IF function limit if you'd used OR:

=IF(C2="HK","JK",IF(OR(C2="CN",C2="TR"),"MF",IF(OR(C2="IT",C2="PH",C2=
"TH"),"DWS",IF(OR(C2="VN",C2="GB",C2="TW",C2="LK",C2="SP"),"CB","SH"))
))

Returns SH for "" and anything else not declared.

But I'd still recommend a VLOOKUP for this type of problem.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top