# Thread: Nested IFs (Excel 2002)

1. ## Nested IFs (Excel 2002)

Is there a limit to the number of IFs you can nest?

I have a bunch of characters I need to evaluate and return one of three words. (I don't seem to be able to use ranges because they're a mix of letters and numbers.) My format is working, but as soon as the formula wraps to a second line, it stops working. The whole thing looks like this: =IF(B2=X123,"ONE",IF(B2=X133,"ONE",IF(B2=X143,"ONE ",IF(B2=X250,"ONE",IF(B2=X350,"ONE",IF(B2=X450,"ON E",IF(B2=X380,"TWO",IF(B2=X390,"TWO",IF(B2=X900,"T WO","THREE"))))))))) --- the values are fictitious, but suffice it to say they're overlapping ranges.

If I shorten the formula so it stays on one line, it works fine, which tells me I probably have the formatting right <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. Of course, I can't do that! <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

Any ideas of why this is happening, how to get around it, or maybe how to do what I need another way? (I'm kind of an intermediate Excel user, btw.)

2. ## Re: Nested IFs (Excel 2002)

7 nested functions: For workarounds see Chip Pearson's site or John Walkenbach's site

Steve

3. ## Re: Nested IFs (Excel 2002)

Thanks so much. That gives me someplace to go, anyway!

4. ## Re: Nested IFs (Excel 2002)

Take a look at VLOOKUP. You can place your values in a table and be able to return the answer based on some criteria.

Steve

6. ## Re: Nested IFs (Excel 2002)

<P ID="edit" class=small>(Edited by JohnBF on 08-Mar-04 12:17. Added then corrected formula.)</P>In appropriate circumstances, =CHOOSE(arg,return if 1,return if 2, return if 3, etc ...) can also be useful for this kind of problem. See if

=CHOOSE(1+OR(B2=X123,B2=X133,B2=X143,B2=X250,B2=X3 50,B2=X450)*2+OR(B2=X380,B2=X390,B2=X900)*3,"THREE ","ONE","TWO")

works for you.

7. ## Re: Nested IFs (Excel 2002)

The nesting limit is 7. This could easily be done by building a table on another worksheet and using VLOOKUP to get the value from the table.

8. ## Re: Nested IFs (Excel 2002)

Actually, I ended up using CONCATENATE, the & form of it. Works like a charm and should be easy enough for others to understand.

Thanks for everyone's replies!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•