vlookup,IF function together

[Ranjit kurian]

Hi
i know to do vlookup, but the problem here is, when i do vlookup function
and if the value is found then the result should be shown as "YES" if not
then "NO"

 

[Peo Sjoblom]

You don't need VLOOKUP for that, you can just use MATCH

=IF(ISNUMBER(MATCH(lookup_value,A1:A100,0)),"YES","NO")

where A1:A100 with be the equivalent of the first column (leftmost) in a
VLOOKUP formula


--


Regards,


Peo Sjoblom

 

[Pete_UK]

Something like this:

=IF(ISNA(your_vlookup_formula),"No","Yes")

You could use MATCH instead of VLOOKUP.

Hope this helps.

Pete

 

[Max]

Something like this should do it for you
Assume your vlookup col (the 1st col) is col A in Sheet1
In Sheet2,
In B1:
=IF(A1="","",IF(ISNUMBER(MATCH(A1,Sheet1!A:A,0)),"Yes","No"))
Copy B1 down

 

[Teethless mama]

=IF(COUNTIF(A:A,"criteria"),"Yes","No")

 

[JE McGimpsey]

One way:

=IF(ISNA(MATCH(<value>,<range>,FALSE)),"NO","YES")

Another:

=IF(COUNTIF(<range>,<value>)>0,"YES","NO")

 

[Kaustubh Kelkar]

Hi Peo,

I think you can help me.
I have one problem.Suppose in A1,A2 and A3 I have digit 3. In B1, there is one digit i.e. 3.
now I use vlookup function in C1 as
=VLOOKUP(A2,B:B,1,0).
I copy C1 in C2 and C3.
In this particular case.it is showing 3 n C1,C2 and C3.
As there is only one 3 in B column, as against three 3s in column A, I want #NA in C2 and C3.
Can something be done in this case?
(I need this for doing bank reconciliation, where there are lots of items and too many numbers are more than one)

Regards
Kaustubh