MS Excel - IF Statement - is there a limit?

Yes, you've reached the limit on nested IFs.

You could use VLOOKUP in this case:

=VLOOKUP(B9, $B$101:$C$124, 2, false)

This will look for B9 in B101:B124, and the first time it finds it, it will return what's in C on the same row.

Good luck.

_______

EDIT: By the way, if there is a chance B9 won't be in B101:B124, you may want to account for that:

=IF(ISERROR(VLOOKUP(B9, $B$101:$C$124, 2, false)), "not found", VLOOKUP(B9, $B$101:$C$124, 2, false))

If Vlookup returns an error (i.e. it didn't find it), this will display a message (e.g "not found"), otherwise it will display the result from column C.

.

Ms Excel If Statement

This Site Might Help You.

RE:

MS Excel - IF Statement - is there a limit?

Here is an odd question:

Here is the formula I am typing below

=IF(B9=$B$101,$C$101),

IF(B9=$B$102,$C$102), etc.

i am typing all the way to $C$108

Everything works fine. BUT: When I try to add in the next sequence: IF(B9=$B$109,$C$109, I receive an error msg:

"The formula...

There is probably a limit to the length of an entry. OpenOffice Calc has the option to define names where they can actually be formulas. See if Excel has the same feature. You can use the names as functions in a formula. For example if the name xxx was defined as =if(a=b;x;y) putting xxx in a function would have the same effect as inserted the complete formula. There may still be the same size limit if the expansion work area has the limit.

I believe you can nest 8 If loops in one cell. You would either have to reference another cell with more IF statements or go to VBA for excel and write some code.