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

<colgroup><col style="width: 30px;"><col style="width: 75px;"><col style="width: 75px;"><col style="width: 75px;"><col style="width: 75px;"><col style="width: 75px;"><col style="width: 75px;"><col style="width: 75px;"><col style="width: 75px;"><col style="width: 75px;"></colgroup><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

please help
<table border="1" cellspacing="0" cellpadding="0" collapse;="" border-spacing:="" 0px;="" font-size:="" 10pt;="" margin-bottom:="" 1em;="" font-family:="" arial,="" arial;="" padding-left:="" 2pt;="" padding-right:="" 2pt;"="" width=""><colgroup><col style="width: 30px;"><col style="width: 75px;"><col style="width: 75px;"><col style="width: 75px;"><col style="width: 75px;"><col style="width: 75px;"><col style="width: 75px;"><col style="width: 75px;"><col style="width: 75px;"><col style="width: 75px;"></colgroup><tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] "]
[TD] [/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"][/TD]
[TD]101 [/TD]
[TD]john[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"][/TD]
[TD]107[/TD]
[TD]smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody></table>

 

[admiral100]

Hi,

Try this:

G1 =IFERROR(INDEX(F1:F50,SEARCH("101",E1)>=1),"")

[TABLE="width: 280"]
<colgroup><col width="70" span="4" style="width:52pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"]E[/TD]
[TD="class: xl63, width: 70"]F[/TD]
[TD="class: xl63, width: 70"]G[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]101 102[/TD]
[TD="class: xl63"]john[/TD]
[TD="class: xl63"]john[/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]107 108[/TD]
[TD="class: xl63"]smith[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]105 101[/TD]
[TD="class: xl63"]Carol[/TD]
[TD="class: xl63"]Carol[/TD]
[/TR]
</tbody>[/TABLE]

 

[jefflee]

Thanks admiral
I found this works.
Thanks again

regards,

Jeff

 

[Aladin Akyurek]

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]101 102[/TD]
[TD]john[/TD]
[TD][/TD]
[TD]

101​

[/TD]
[TD]Carol[/TD]
[TD]john[/TD]
[TD]john, Carol[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]107 108[/TD]
[TD]smith[/TD]
[TD][/TD]
[TD]

108​

[/TD]
[TD]smith[/TD]
[TD]smith[/TD]
[TD]smith[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]105 101[/TD]
[TD]Carol[/TD]
[TD][/TD]
[TD]

102​

[/TD]
[TD]john[/TD]
[TD]john[/TD]
[TD]john[/TD]
[/TR]
</tbody>[/TABLE]


Chose whichever output is the most convenient...

In E1 just enter and copy down:

=LOOKUP(9.99999999999999E+307,FIND(" "&D1&" "," "&$A$1:$A$3&" "),$B$1:$B$3)

In F1 control+shift+enter, not just enter, and copy down:

=INDEX($B$1:$B$3,MATCH("* "&D1&" *"," "&$A$1:$A$3&" ",0))

In G1 control+shift+enter, not just enter, and copy down:

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(FIND(" "&D1&" "," "&$A$1:$A$3&" ")),$B$1:$B$3,""))