Thread: VLOOKUP Problem

1. VLOOKUP Problem

Register for a FREE account, and/
or Log in to avoid these ads!

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?

2. 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

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

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

3. Originally Posted by 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

Code:
`=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!

4. 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.

5. 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.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•