Excel - Search two columns in list for first and last name and return Id

Asked By Johno on 21-Feb-10 08:43 PM
I maintain a database log to track changes in contact data.  The log contains
last names in column B, first names in column C and ID# in column D .  I also
have a master list (Last Name, First Name and ID#) of all workers which I
have pasted into columns AA, AB and AC.  I would like to be able to have a
formula in column D which searches the master list for the matching first and
last names and returns the ID number in column C or a "?" if there is no
match.  The formula below, which I have used to successfully search for last
names works unless there are two last names the same.   I know I need an AND
in there to search for first names also but I do not know where (or how) to
put it.


Database Log
Master List
B            C           D
AA         AB          AC
Last       First        ID#
Last        First       ID#

Smith      John       ?
Blake        Tod      12056
Jones       Mary      ?
Jones        Mary     65749
Blake        Tod       ?
Park         Judy      54682
Park         Judy       ?
Smith        John     34628

I would appreciate some help.  I am using excel 2007


Gary Keramidas replied to Johno on 21-Feb-10 09:28 PM
you could use  helper columns where you concatenate the names.

Gary Keramidas
Excel 2003
NBVC replied to Johno on 21-Feb-10 09:30 PM

VBA Code:


or if your ID's are actually numeric, then perhaps even:

VBA Code:



Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
NBVC's Profile: 74
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=181328

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]
Johno replied to Gary Keramidas on 21-Feb-10 10:13 PM
That will not work in this case.  I can always concatenate the master list but I
have inexperienced volunteers entering data into the log and they would have
to enter the data in concatenated form.  Too much for them to absorb.

Thanks for the response
Johno replied to NBVC on 21-Feb-10 10:55 PM
The second does not work but the first one sems to.  I will let me know if there
are any problems,

Thank you, thank you.

T. Valko replied to NBVC on 21-Feb-10 11:51 PM
Try this array entered** version:


** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Microsoft Excel MVP