Search for B1 in A1:A8 and return value in from A

[faresar]

Hello guys,

I have this challenge that i am trying to tackle.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]tele
[/TD]
[TD]tele
[/TD]
[/TR]
[TR]
[TD]Tele
[/TD]
[TD]bla
[/TD]
[/TR]
[TR]
[TD]bla
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fa, bla,tele,
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ka, ha, tele
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]la, ra, tele,
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]tele
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]tele bla
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

i want a command that..

1- looks for B1 cell inside A1:A8 range
2- If it finds a match return whatever value that is in A

Basically the command should be able to find 7 matches for "tele" out of 8. I intend to use the command inside another complex code. If i drag down the command, it should find 3 matches for B2.

Could you help me with that. I am not that good with arrays and searching for keywords. I tried vlookup and index/match but it was able to find only 3 matches. Also i dont know how to return value from A.

This is just an example. I intend to use this with 20k rows

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]ITEM[/td][td]tele[/td][td]bla[/td][/tr]

[tr][td]

2​

[/td][td]tele[/td][td]

7​

[/td][td]

3​

[/td][/tr]

[tr][td]

3​

[/td][td]Tele[/td][td]#LIST#[/td][td]#LIST#[/td][/tr]

[tr][td]

4​

[/td][td]bla[/td][td]tele[/td][td]bla[/td][/tr]

[tr][td]

5​

[/td][td]fa, bla,tele,[/td][td]Tele[/td][td]fa, bla,tele,[/td][/tr]

[tr][td]

6​

[/td][td]ka, ha, tele[/td][td]fa, bla,tele,[/td][td]tele bla[/td][/tr]

[tr][td]

7​

[/td][td]la, ra, tele,[/td][td]ka, ha, tele[/td][td][/td][/tr]

[tr][td]

8​

[/td][td]tele[/td][td]la, ra, tele,[/td][td][/td][/tr]

[tr][td]

9​

[/td][td]tele bla[/td][td]tele[/td][td][/td][/tr]

[tr][td]

10​

[/td][td][/td][td]tele bla[/td][td][/td][/tr]

[tr][td]

11​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

B2, just enter and copy to B2:

=COUNTIF($A$2:$A$9,"*"&B1&"*")

B4, control+shift+enter, not just enter, copy to C4, and down:

=IF(ROWS(B$4:B4)<=B$2,INDEX($A$2:$A$9,
    SMALL(IF(ISNUMBER(SEARCH(B$1,$A$2:$A$9)),
    ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(B$4:B4))),"")