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:

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

A​

[/td][td]

B​

[/td][td]

C​

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

1​

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

[tr][td]

2​

[/td][td]Flame Red (984-19959)[/td][td][/td][td]

2​

[/td][/tr]

[tr][td]

3​

[/td][td]French Blue Gloss (984-32725)[/td][td][/td][td]Flame Red (984-19959)[/td][/tr]

[tr][td]

4​

[/td][td]Lemon Yellow (984-32120)[/td][td][/td][td]Signal Red (984-50735)[/td][/tr]

[tr][td]

5​

[/td][td]Mistletoe Gloss (984-51040)[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]Navy (984-50282)[/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]Orange (984-51439)[/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]Anodic Bronze (961-19930)[/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]Pommel Blue (984-51034)[/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]Safety Yellow Gloss (984-2015G)[/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]Signal Red (984-50735)[/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]Space Blue (984-19990)[/td][td][/td][td][/td][/tr]

[tr][td]

13​

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

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.