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

  • Thread starter Thread starter Johno
  • Start date Start date
J

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


NBVC said:
Try:
Click to expand...
VBA Code:
--------------------



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




or if your ID's are actually numeric, then perhaps even:
Click to expand...
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),"?")
Click to expand...

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


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

.
Click to expand...
 
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))))
Click to expand...

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


NBVC said:
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
Click to expand...
 
Back
Top