From: vinstream on 8 Apr 2010 03:21 I have 2 spreadsheets with names and email addresses - File 1: 150 records File 2: 63000 records I need to check if each of the emails on file 1 are there in file 2 or not. If it is there it shoul show "yes" else show "no" when I do a Vlookup it gives me the email ids in the cell - I am not able to show a 'yes' or 'no' so I guess this will hav eto be a combination of vlookup and IF statement.....any ideas? From: Roger Govier on 8 Apr 2010 04:00 Hi No need for a slow lookup. Assuming data in column A of both sheets On sheet1 in B1 =IF(COUNTIF(Sheet2!A:A,A1),"yes","no") Copy down for the 150 rows of data that you have on that sheet -- Regards Roger Govier vinstream wrote:> I have 2 spreadsheets with names and email addresses - > > File 1: 150 records > File 2: 63000 records > > I need to check if each of the emails on file 1 are there in file 2 or > not. If it is there it shoul show "yes" else show "no" > > when I do a Vlookup it gives me the email ids in the cell - I am not > able to show a 'yes' or 'no' so I guess this will hav eto be a > combination of vlookup and IF statement.....any ideas? > > > From: Mick Henn on 8 Apr 2010 04:10 This is pretty straight forward. The formula should be "=if(isna(vlookup(cell in file1),(range in file 2),1,false)),"no","yes") So the function does the lookup, and if the result of the lookup is #N/A (i.e. the mail is not in the second file) then it returns the value of "no". If the lookup result is anything other than #N/A it will return a value of "yes". "vinstream" wrote: > I have 2 spreadsheets with names and email addresses - > > File 1: 150 records > File 2: 63000 records > > I need to check if each of the emails on file 1 are there in file 2 or > not. If it is there it shoul show "yes" else show "no" > > when I do a Vlookup it gives me the email ids in the cell - I am not > able to show a 'yes' or 'no' so I guess this will hav eto be a > combination of vlookup and IF statement.....any ideas? > > > > . > From: vinstream on 8 Apr 2010 04:21 Thanks. This worked. Regards, Vinstream On Apr 8, 1:00 pm, Roger Govier wrote:> Hi > > No need for a slow lookup. > Assuming data in column A of both sheets > > On sheet1 in B1 > =IF(COUNTIF(Sheet2!A:A,A1),"yes","no") > Copy down for the 150 rows of data that you have on that sheet > -- > Regards > Roger Govier > > > > vinstream wrote: > > I have 2 spreadsheets with names and email addresses - > > > File 1: 150 records > > File 2: 63000 records > > > I need to check if each of the emails on file 1 are there in file 2 or > > not. If it is there it shoul show "yes" else show "no" > > > when I do a Vlookup it gives me the email ids in the cell - I am not > > able to show a 'yes' or 'no' so I guess this will hav eto be a > > combination of vlookup and IF statement.....any ideas?- Hide quoted text - > > - Show quoted text -  |