IF and VLOOKUP formula (Excel 2002)

Topic

New Reply

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

Reply | Quote

Replies

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”,”yes”,”no”))

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

Steve

Reply | Quote

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

Reply | Quote

It was Steve, not I!

Reply | Quote

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

Reply | Quote

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

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

or

=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"))

Whether you need the latter, more complicated formula depends on whether the VLOOKUP may return another valid value than “x”.

Reply | Quote