VLOOKUP Problem

[dahowarduk]

I have 2 lists of names.
One is called NEWNAMES and the other is called OLDNAMES.

I want to check if a person in the OLDNAMES list is in the list of NEWNAMES and if it is then allocate that person the 'value' 1, and if not then the value 0.

Using vlookup I can get the 'value' 1 if the person is indeed listed in NEWNAMES, put I get the 'value' #N/A if the person is not listed in NEWNAMES.


Any ideas?

[tommyt61]

maybe this will work for you .... This formula works based on the following

Old Names start in D3
New Names start in F3

copy this formula into C3 and copy down

=IF(D3="","",COUNTIF($F$3:$F$210,D3))

you can adjust $f$210 if you need to i just picked a random stopping point .

[dahowarduk]

maybe this will work for you .... This formula works based on the following

Old Names start in D3
New Names start in F3

copy this formula into C3 and copy down

=IF(D3="","",COUNTIF($F$3:$F$210,D3))

you can adjust $f$210 if you need to i just picked a random stopping point .

Brilliant. Thanks very much.
I'Ve never used countif before so a new skill learnt today!

[juan.suarez]

With lookup it works. Put this formula in the F5 cell.

=IF(ISERROR(VLOOKUP(E5,$D$5:$D$8,1,FALSE)),0,1)

In E5 begins the old names
And between D5 and D8 are the new names.

[dahowarduk]

Brilliant...that works as well.
I've not used If (iserror) before so again another new skill learnt today.
Your solution solves the vlookup dilemna of vlookup returning #N/A when an element cannot be found.
Many thanks.