VLOOKUP Problem

dahowarduk

New member
Joined
Jul 15, 2012
Messages
19
Reaction score
0
Points
1
Location
UK
Excel Version(s)
2104
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

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
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 .
 

dahowarduk

New member
Joined
Jul 15, 2012
Messages
19
Reaction score
0
Points
1
Location
UK
Excel Version(s)
2104
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!
 

juan.suarez

New member
Joined
Jul 17, 2012
Messages
6
Reaction score
0
Points
0
Location
Colombia
Website
www.myexceltutorial.com
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

New member
Joined
Jul 15, 2012
Messages
19
Reaction score
0
Points
1
Location
UK
Excel Version(s)
2104
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.
 
Top