Prev: vlookup + if
Next: Drop down/data validation list
From: מיכאל (מיקי) אבידן on 2 Apr 2010 12:04
In cell B1 of Sheet1 try the formula:
=IF(ISNA(MATCH(A1,Sheet2!A:A,)),"YES","NO")
Micky


"sross002" wrote:

> I have a list of names on sheet2 (a1:a69) and if the name in cell a1 (sheet1)
> matches any of those names then I want it to say "NO" in cell a2 (sheet1).
> if it doesn't match then I want it to say "YES" in cell a2 (sheet1).
>
> I'm assuming it is a combination of a "vlookup" and a "if" formula, but I
> can't right it out. Please help.
From: T. Valko on 2 Apr 2010 12:03
>I have
>
=IF(a2=vlookup(a2,sheet1!$B$2:$B$B69,1,FALSE),"NO", "YES")
>why isn't YES showing up where it should be?

Because:

vlookup(a2,sheet1!$B$2:$B$B69,1,FALSE)

Is returning the #N/A error meaning it can't find A2 in the range
sheet1!$B$2:$B$B69.

See my other reply for a better method.

--
Biff
Microsoft Excel MVP


"sross002" <sross002(a)discussions.microsoft.com> wrote in message
news:5BC9CD1C-FE15-402A-8113-B5B0BF4F4CB6(a)microsoft.com...
>I have
> =IF(a2=vlookup(a2,sheet1!$B$2:$B$B69,1,FALSE),"NO", "YES")
>
> It's coming back with "NO" where it should be BUY instead of "YES" it says
> #N/A.
>
> What am I doing wrong, why isn't YES showing up where it should be?
>
>
> "sross002" wrote:
>
>> I have a list of names on sheet2 (a1:a69) and if the name in cell a1
>> (sheet1)
>> matches any of those names then I want it to say "NO" in cell a2
>> (sheet1).
>> if it doesn't match then I want it to say "YES" in cell a2 (sheet1).
>>
>> I'm assuming it is a combination of a "vlookup" and a "if" formula, but I
>> can't right it out. Please help.


From: sross002 on 2 Apr 2010 12:12
THANKS!

"מיכאל (מיקי) אבידן" wrote:

> In cell B1 of Sheet1 try the formula:
> =IF(ISNA(MATCH(A1,Sheet2!A:A,)),"YES","NO")
> Micky
>
>
> "sross002" wrote:
>
> > I have a list of names on sheet2 (a1:a69) and if the name in cell a1 (sheet1)
> > matches any of those names then I want it to say "NO" in cell a2 (sheet1).
> > if it doesn't match then I want it to say "YES" in cell a2 (sheet1).
> >
> > I'm assuming it is a combination of a "vlookup" and a "if" formula, but I
> > can't right it out. Please help.
First  |  Prev  | 
Pages: 1 2
Prev: vlookup + if
Next: Drop down/data validation list