if statement with vlookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

dear all, hope can help me

=IF(VLOOKUP(A3,$E$4:$E$5,1,FALSE)=A3,"found", "not found"

why "not found" unable to display in the cell but only showing "n/a" if not found? if true, "found" is shown, false only show "n/a". why???
 
Try:

=IF(NOT(ISNA(VLOOKUP(A3,$E$4:$E$5,1,FALSE))),"found", "not found")

--

Vasant


ek said:
dear all, hope can help me.

=IF(VLOOKUP(A3,$E$4:$E$5,1,FALSE)=A3,"found", "not found")

why "not found" unable to display in the cell but only showing "n/a" if
Click to expand...
not found? if true, "found" is shown, false only show "n/a". why???
 
Hi Vasant

just a minor addition. I would use
=IF(ISNA(MATCH(A3,$E$4:$E$5,0)),"not found", "found")

saving one function call :-)
 
Oh, no! You too? I thought I only needed to watch out for Harlan complaining
about unneeded function calls! <vbg>

Seriously, I know mine was a somewhat inelegant, although I think easier for
novices to understand.

Regards,

Vasant.
 
Hi Vasant
couldn't resist - after receiving this kind of responses regulary from
Harlan :-)

And of course I know you knew <vbg>
Frank
 
just a minor addition. I would use
=IF(ISNA(MATCH(A3,$E$4:$E$5,0)),"not found", "found")
Click to expand...
...

As my younger daughter would say, vomitrocious!

=IF(COUNTIF($E$4:$E$5,A3),"found","not found")

If you're going to get picky, learn to do it right. If clarity is essential,
then better to use an array formula.

=IF(OR(A3=$E$4:$E$5),"found","not found")
 
Harlan said:
...
..

As my younger daughter would say, vomitrocious!
Click to expand...

Hi Harlan
to be honest even my online dictionary wasn't able to explain
'vomitrocious' :-)


=IF(COUNTIF($E$4:$E$5,A3),"found","not found")
Click to expand...

You won on this!
If you're going to get picky, learn to do it right. If clarity is
essential, then better to use an array formula.

=IF(OR(A3=$E$4:$E$5),"found","not found")
Click to expand...

Personal thought: Don't think an array formula is more clear than your
COUNTIF suggestion
Frank
 
...
...
Personal thought: Don't think an array formula is more clear than your
COUNTIF suggestion
Click to expand...

To us, perhaps not. However, it's a question of whether someone less experienced
would find

COUNTIF(List,Something) or OR(Something=List)

clearer. There's a lot of implicit understanding with the former (that a count >
0 means there's at least one match, and that a number <> 0 is interpretted the
same as TRUE). The latter is unambiguous.
 
Back
Top