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?

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?

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?



.

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 -