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

neveu

Board Regular
Joined
Jan 27, 2009
Messages
225
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
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,""))

19319629.jpg

Formula in D2 : (confirm CTRL+SHIFT+ENTER) copied down:
Code:
=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:
Code:
=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))),"")
 
Upvote 0

neveu

Board Regular
Joined
Jan 27, 2009
Messages
225
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.

92239104.png


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

Forum statistics

Threads
1,186,822
Messages
5,960,012
Members
438,457
Latest member
Just Brian

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top