vlookup + if formula help!

S

sross002

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.
 
M

Ms-Exl-Learner

Copy and paste the below formula in Sheet1 A2 cell
=IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$A$69,1,FALSE)),"YES",IF(VLOOKUP(A1,Sheet2!$A$1:$A$69,1,FALSE)=A1,"NO","")))

Remember to Click Yes, if this post helps!
 
S

sross002

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?
 
T

T. Valko

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.
 
×

מיכ×ל (מיקי) ×בידן

In cell B1 of Sheet1 try the formula:
=IF(ISNA(MATCH(A1,Sheet2!A:A,)),"YES","NO")
Micky
 
S

sross002

THANKS!

מיכ×ל (מיקי) ×בידן said:
In cell B1 of Sheet1 try the formula:
=IF(ISNA(MATCH(A1,Sheet2!A:A,)),"YES","NO")
Micky
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top