Prev: I want to link sheets in excel to a master sheet in excel
Next: Excel 2007 - Customised Menu
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 <ro...(a)technology4nospamu.co.uk>
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 -

 | 
Pages: 1
Prev: I want to link sheets in excel to a master sheet in excel
Next: Excel 2007 - Customised Menu