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

#### neveu

##### Board Regular
hi all,

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
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:
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))),"")``

#### neveu

##### Board Regular
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.

neveu
http://img64.imageshack.us/img64/8150/92239104.png

Replies
1
Views
169
Replies
7
Views
351
Replies
3
Views
135
Replies
2
Views
580
Replies
3
Views
378

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.

### Which adblocker are you using?

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

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