# IF Formula returns #N/A even when criteria if criteria not met

1. ## IF Formula returns #N/A even when criteria if criteria not met

I have set if all things fail/ FALSE, it should produce result "accessories" so why is it still showing #N/A? I'm yanking my hair out!

2. ## Re: IF Formula returns #N/A even when criteria if criteria not met

its probably one of the results set is not correct
and IFERROR ( formula, "result if error") maybe the way to go

i would test each one of your IF tests and see what happens
you are not testing for the result to not return any result

for example
=VLOOKUP('Q1''13 Supporting'!\$P19,Lists!\$T\$2:\$U\$29,2,FALSE)
returns a #N/A
so does
=MATCH('Q1''13 Supporting'!\$P19,Lists!\$T\$2:\$T\$29,0)
and so thats not true or false

4. ## Re: IF Formula returns #N/A even when criteria if criteria not met

When the MATCH formula doesn't find a match, you get an error. Instead try:

=IF(\$I21>25000,"Systems",IF('Q1''13 Supporting'!\$P19="","Accessories",IFERROR(IF(MATCH('Q1''13 Supporting'!\$P19,Lists!\$T\$2:\$T\$29,0),VLOOKUP('Q1''13 Supporting'!\$P19,Lists!\$T\$2:\$U\$29,2,FALSE)),"Accessories")))

5. ## Re: IF Formula returns #N/A even when criteria if criteria not met

I was thinking that the formula is smart enough to know that if it doesn't match then it reflects "Accessories"
However it returns N/A if there's no match.

6. ## Re: IF Formula returns #N/A even when criteria if criteria not met

Thanks zumbalj ! It worked great and resolved the issue with the red cell! Cheers! You are a genius!

7. ## Re: IF Formula returns #N/A even when criteria if criteria not met

Thanks! Glad to be of help Remember to mark your thread solved, and if you feel so inclined, press the * on the left side to add to my reputation.

8. ## Re: IF Formula returns #N/A even when criteria if criteria not met

Hi zumbalj, I have clicked the *

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1