IFERROR and Multiple IF Statements Not Returning Expected Result

**eforsadoko** · 07-02-2013, 08:43 PM

I am using this formula in column C

=IFERROR(VLOOKUP(B2,'NewData'!A:V,11,FALSE),"NA")

I am using this formula in column D

=IF(C2<=100,"1",IF(C2>300,"3",IF(C2>100,"2","NA")))

It is failing to recognize the NA results and instead showing 3 as the result.

What have I got wrong? Thank you.

**benishiryo** · 07-02-2013, 09:13 PM

hi eforsadoko, welcome to the forum. first question; why do you need to range up to Column V when you only need the 11th column?
=IFERROR(VLOOKUP(B2,NewData!A:V,11,FALSE),"NA")

text in excel are considered to be bigger than numbers. you can try somewhere:
="ABC">0

so your first test of lesser or equals to 100 will fail. a text is greater than a number. it goes to the 2nd test where you put whether is it greater than 300. it is, so it returns "3". put it this way instead:
=IF(C2="NA","NA",IF(C2<=100,1,IF(C2>300,2,100)))

so only when it fails the first test, it will continue with the rest. also, you don't need double quotes for numbers:
=IF(C2<=100,"1",IF(C2>300,"3",IF(C2>100,"2","NA")))

**eforsadoko** · 07-02-2013, 09:29 PM

Your solution is very fine. I tried it and it works save for one thing: if the field is blank it shows a 1. Do I need add something to cover a blank field?

**eforsadoko** · 07-02-2013, 09:41 PM

I ran into a some trouble with your formula so edited it:

From
=IF(C2="NA","NA",IF(C2<=100,1,IF(C2>300,2,100)))

To:
=IF(C2="NA","NA",IF(C2<=100,1,IF(C2>300,3,2)))

It kept giving me 100s where not so.

**jhren** · 07-02-2013, 09:47 PM

Yes, but what do you want it to return... NA or blank or something else?

If NA...

=IF(OR(C2="",C2="NA"),"NA",IF(C2<=100,1,IF(C2>300,3,2)))

If blank...

=IF(C2="","",IF(C2="NA","NA",IF(C2<=100,1,IF(C2>300,3,2))))

If something else, put it between the second set of quotation marks (red ones) if textual... if number, replace the quotation marks with the number.