Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 16
Default 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 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:A A$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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 72
Default Search two columns in list for first and last name and return Id


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


Gary Keramidas
Excel 2003


"Johno" wrote in message
...
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:A A$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


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 16
Default Search two columns in list for first and last name and return

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


"Gary Keramidas" wrote:


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


Gary Keramidas
Excel 2003


"Johno" wrote in message
...
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:A A$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


.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
search value in 2 columns return value in another jmegdan1 Excel Worksheet Functions 3 November 6th 08 12:57 AM
Search for & return list of values plb2862 Excel Worksheet Functions 12 March 28th 07 12:20 AM
Search table for string return next columns value mikpits Excel Worksheet Functions 3 January 19th 07 08:05 AM
Need Help!! Want to search through 3 columns for a list of keywords The Moose Excel Worksheet Functions 7 January 2nd 07 04:12 AM
Search Multiple columns for criterion asterisk (*) and Return Numeric Label Sam via OfficeKB.com Excel Worksheet Functions 8 July 14th 06 06:56 PM


All times are GMT +1. The time now is 08:32 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"