Search Substring within column of text strings and list all results

[fchiav]

Hi, I'm fairly new to this forum.

I would appreciate assistance on improving my formula that works similar to the "Find & Select" (Binoculars) tool in Excel.
The formula partially works at present with some sub-strings, though with others does not display any or all the results that it should.

=IFERROR(INDEX($F$5:$F$219, SMALL(IF(ISNUMBER(SEARCH($B$1, $I$5:$I$219,1)), MATCH(ROW($I$5:$I$219), ROW($I$5:$I$219))), ROW())),"")
[Code]/


For Example searching for "Red" in the list below (combined colour names & colour codes), does not yeild the result for Flame Red or Signal Red, however does list some other red colour names on the full list (over 400 text strings). I want to be able to search either part colour text or part colour codes.


Flame Red (984-19959)
French Blue Gloss (984-32725)
Lemon Yellow (984-32120)
Mistletoe Gloss (984-51040)
Navy (984-50282)
Orange (984-51439)
Anodic Bronze (961-19930)
Pommel Blue (984-51034)
Safety Yellow Gloss (984-2015G)
Signal Red (984-50735)
Space Blue (984-19990)

Thanking you in advance.

 

[Aladin Akyurek]

Are these values are single entries in one range?

 

[fchiav]

Are these values are single entries in one range?

Each line is a cell, & all cells in one column. So I guess that would be a range.

 

[Aladin Akyurek]

Each line is a cell, & all cells in one column. So I guess that would be a range.

The non-working formula you posted suggests in fact two different ranges!... That said:

Row\Col	A​	B​	C​
1​			Red
2​	Flame Red (984-19959)		2​
3​	French Blue Gloss (984-32725)		Flame Red (984-19959)
4​	Lemon Yellow (984-32120)		Signal Red (984-50735)
5​	Mistletoe Gloss (984-51040)
6​	Navy (984-50282)
7​	Orange (984-51439)
8​	Anodic Bronze (961-19930)
9​	Pommel Blue (984-51034)
10​	Safety Yellow Gloss (984-2015G)
11​	Signal Red (984-50735)
12​	Space Blue (984-19990)
13​

C2, control+shift+enter (cse), not just enter:

=SUM(IF(ISNUMBER(SEARCH(C$1,$A$2:$A$12)),1))

C3, cse and copy down:

=IF(ROWS($A$2:A2)<=$C$2,
    INDEX($A$2:$A$12,SMALL(IF(ISNUMBER(SEARCH(C$1,$A$2:$A$12)),
    ROW($A$2:$A$12)-ROW($A$2)+1),ROWS($A$2:A2))),"")

 

[fchiav]

Thank You Aladin, problem has been solved.
This finally works Beautifully now.

 

[Aladin Akyurek]

Thank You Aladin, problem has been solved.
This finally works Beautifully now.

Great to hear that.