Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search value in 2 columns return value in another | Excel Worksheet Functions | |||
Search for & return list of values | Excel Worksheet Functions | |||
Search table for string return next columns value | Excel Worksheet Functions | |||
Need Help!! Want to search through 3 columns for a list of keywords | Excel Worksheet Functions | |||
Search Multiple columns for criterion asterisk (*) and Return Numeric Label | Excel Worksheet Functions |