vlookup + if formula help!
in [General]
|
Prev: vlookup + if
Next: Drop down/data validation list
From: sross002 on 2 Apr 2010 11:35 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 11:42 Try this... =IF(COUNTIF(Sheet2!A1:A69,A1),"No","Yes") -- Biff Microsoft Excel MVP "sross002" <sross002(a)discussions.microsoft.com> wrote in message news:10AFAFB9-4B18-436C-8F7B-DF21DE92CD93(a)microsoft.com... >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: Ms-Exl-Learner on 2 Apr 2010 11:51 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! -------------------- (Ms-Exl-Learner) -------------------- "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 11:54 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:01
I am getting an error. I have a MAC, is that why? "sross002" wrote: > 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. |