list of similar email adress based on first name and last name

[neveu]

hi all,
i would really appreciate your help/advice with the following chalenge:

i have a rather large list (over 600 items).
the problem is as follow:
based on the first name and last name i would need to search in a data base what are the matching email adress:
so for example if I have
Peter Clay i would need to return from the database a 2 lists:
one with every email that contains peter: peter_x@contact.com, peter_y@contact.com and another email list with all the persons that contains clay : allan_clay@contact.com, robert_clay@contact.com etc.

any imputs would be really appreciated

 

[Robert Mika]

With a helper method (column A)(formula in A1=IF(ISNUMBER(SEARCH($D$1,C2)),$D$1,IF(ISNUMBER(SEARCH($E$1,C2)),$E$1,""))

Formula in D2 : (confirm CTRL+SHIFT+ENTER) copied down:

=IF(ROWS($D$2:D2)<=COUNTIF($A$2:$A$8,$D$1),INDEX($C$2:$C$8,SMALL(IF($A$2:$A$8=$D$1,ROW($A$2:$A$8)-ROW($A$2)+1),ROWS($G$2:G2))),"")

in E2:

=IF(ROWS($D$2:E2)<=COUNTIF($A$2:$A$8,$E$1),INDEX($C$2:$C$8,SMALL(IF($A$2:$A$8=$E$1,ROW($A$2:$A$8)-ROW($B$2)+1),ROWS($G$2:H2))),"")

 

[neveu]

hi Robert,
your ideea seems to work. however i think i need to give you some additional information of what i need to do with my data.
below is an example of how my data is stored.in this example i have filled in just the first 4 names I'm trying to search matching emails.
besides this, i have a different workbook with information containing 1st names, last names, emails adress of all the people in the company.
So what i need to to is to search based on the 1st name and last name of the names in row one for matching emails in the data base i have and to display below each name, the matching email - like for the first record there - Mark A Jameson.

thank you in advance
neveu
http://img64.imageshack.us/img64/8150/92239104.png