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"

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


"Ranjit kurian" wrote in message
...
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"

Something like this:

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

You could use MATCH instead of VLOOKUP.

Hope this helps.

Pete

On May 13, 3:55*pm, Ranjit kurian
wrote:
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"

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ranjit kurian" wrote:
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"

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


"Ranjit kurian" wrote:

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"

One way:

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

Another:

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


In article ,
Ranjit kurian wrote:

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"

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