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

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.

=IF(ISNA(INDEX(AC$1:AC$9999,MATCH(B10,AA$1:AA$9999,FALSE))),"?",(INDEX(AC$1:AC$9999,MATCH(B10,AA$1:AA$9999,FALSE))))

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

johno

you could use  helper columns where you concatenate the names.
--


Gary Keramidas
Excel 2003

Try:



VBA Code:
--------------------


=IF(ISNA(MATCH(1,INDEX((AA$1:AA$9999=B10)*(AB$1:AB$9999=C10),0),FALSE)),"?",(INDEX(AC$1:AC$9999,MATCH(1,INDEX((AA$1:AA$9999=B10)*(AB$1:AB$9999=C10),0),FALSE))))
--------------------




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



VBA Code:
--------------------


=IF(COUNTIFS(AA$1:AA$9999,B10,AB$1:AB$9999,C10),SUMIFS(AC$1:AC$9999,AA$1:AA$9999,B10,AB$1:AB$9999,C10),"?")
--------------------


--
NBVC

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]

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

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.

Johno

Try this array entered** version:

=IFERROR(INDEX(AC$1:AC$9999,MATCH(1,IF(AA$1:AA$9999=B10,IF(AB$1:AB$9999=C10,1)),0)),"?")

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

--
Biff
Microsoft Excel MVP