# Thread: IF and VLOOKUP formula (Excel 2002)

1. ## IF and VLOOKUP formula (Excel 2002)

Hi,
I'm having a problem with the following formula:

=IF(VLOOKUP(A2,list!\$K\$2:\$M\$10,3,FALSE)="x","yes", "no")

It is giving a result of "yes" if the vlookup is an "x", however all my other values show up as #N/A, as opposed to my expectations of it being a "no" (as this is the "otherwise" part of the formula). I've tried playing around with my parenthesis, but have had no success. Anyone know what I'm doing wrong?

Thanks!
Lana

2. ## Re: IF and VLOOKUP formula (Excel 2002)

If the item A2 in not in the list, the Vlookup will yield an error and thus the result will be an error

You can try:
=IF(iserror(VLOOKUP(A2,list!\$K\$2:\$M\$10,3,FALSE))," no",IF(VLOOKUP(A2,list!\$K\$2:\$M\$10,3,FALSE)="x","ye s","no"))

If not found it will give a no. If found and = x then a yes, otherwise a no

Steve

3. ## Re: IF and VLOOKUP formula (Excel 2002)

If the value of A2 cannot be found in list!K2!M10, VLOOKUP will return #N/A (not available), and all formulas using this will return #N/A too. Try
<code>
=IF(ISNA(VLOOKUP(A2,list!\$K\$2:\$M\$10,3,FALSE)),"no" ,"yes")
</code>
or
<code>
=IF(ISNA(VLOOKUP(A2,list!\$K\$2:\$M\$10,3,FALSE)),"no" ,IF(VLOOKUP(A2,list!\$K\$2:\$M\$10,3,FALSE)="x","yes", "no"))
</code>
Whether you need the latter, more complicated formula depends on whether the VLOOKUP may return another valid value than "x".

4. ## Re: IF and VLOOKUP formula (Excel 2002)

Yep, this makes sense!! I knew I was overlooking the obvious... thanks Hans!
Lana

5. ## Re: IF and VLOOKUP formula (Excel 2002)

It was Steve, not I!

6. ## Re: IF and VLOOKUP formula (Excel 2002)

This works great Steve... thanks for your help!!
Lana

#### Posting Permissions

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