return the value that the text is contained in a cell

[jefflee]

Hi,

I have the data hereunder

	E	F	G	H	I	J	K	L	M
101 102	john
107 108	smith

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]17[/TD]

</tbody>

requirment :

I want to use lookup or match to return john which 101 is contained in e1
what formula should I use ?
i used if(isnumber(find......

but could not make it

	E	F	G	H	I	J	K	L	M
101	john
107	smith

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]17[/TD]

</tbody>

 

[sheetspread]

Excel 2010
	E	F
1	101 102	john
2	107 108	smith
3
4
5	101	john
6	107	smith
Sheet6

Cell Formulas
Range	Formula
F5	=VLOOKUP("*"&E5&"*",$E$1:$F$2,2,0)

 

[jefflee]

HI, sheetspread
Thanks very very much , worked for hours but invain
Your help saves me hours ans hours .

regards,

Jeff