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

[Johno]

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 don't 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

 

[Gary Keramidas]

you could use helper columns where you concatenate the names.

 

[NBVC]

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)

 

[Johno]

That won't 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 doesn't work but the first one sems to. I'll let me know if there
are any problems,

Thank you, thank you.

Johno

Try:

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



--------------------




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

Microsoft Office Help

.

 

[T. Valko]

I am using excel 2007

=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))))

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

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

Microsoft Office Help