Formula

[Jones1413]

Hello,

I am looking for a formula to search the email addresses in cell A1 in Sheet 1 and return the value of the names from Column A in Sheet 2. I would like for the return value to be in Column B of sheet 1.

Sample.xlsx
	A
2
Sheet1

Sample.xlsx
	A
5
Sheet2

 

[awoohaw]

Hi there. When you use the copy function of the xl2bb add in, you need to select your ranges. As you see all you have provided are a blank cell on two worksheets.
Please update your sample data.

 

[Jones1413]

Sample.xlsx
	A	B
1	johnsmith@aol.com; richards@gmail.com; jones@hotmail.com; smith@yahoo.com
2
3
4
5
6
Sheet1

Sample.xlsx
	A	B
1	John Smith	johnsmith@aol.com
2	Bill Richards	richards@gmail.com
3	Ben Jones	jones@hotmail.com
4	Robert Smith	smith@yahoo.com
5
6
Sheet2

 

[awoohaw]

I just did it all on one sheet. You can move the formulas and cell references around as you need. I also had blank spaces when I copied your data over. So, you may not need the SUBSTITUTE function in your formula.

But, try this:

Book2
	A	B	C	D	E	F
1	John Smith	johnsmith@aol.com
2	Bill Richards	richards@gmail.com
3	Ben Jones	jones@hotmail.com
4	Robert Smith	smith@yahoo.com
5
6	johnsmith@aol.com; richards@gmail.com; jones@hotmail.com; smith@yahoo.com
7
8	John Smith
9	Bill Richards
10	Ben Jones
11	Robert Smith
12
13
Sheet2

Cell Formulas
Range	Formula
A8:A11	A8	=INDEX(A1:A4,MATCH(TEXTSPLIT(SUBSTITUTE(A6," ",""),,";",1,1),B1:B4,0))
Dynamic array formulas.