Search Substring within column of text strings and list multiple results

**Fabchiav** · 05-21-2015, 02:57 AM

I have applied a formula to search a substring within an array. Formula is producing results, though not all matches are appearing as result. I have tried the usual traps, spaces, formats...etc. Still cant figure out why only partial results are being listed. Would appreciate assistance on this.

Thanking you in advance.

**:) Sixthsense :)** · 05-21-2015, 03:07 AM

In B4 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

=IFERROR(INDEX($E$5:$E$129,SMALL(IF(ISNUMBER(SEARCH($B$1,$H$5:$H$129)),ROW($H$5:$H$129)-ROW($E$5)+1),ROWS($1:1))),"")

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

In C4 Cell - Normal Formula Enter is enough

=IFERROR(VLOOKUP(B4,$E$5:$F$129,2,FALSE),"")

Drag both the formula's down

**XOR LX** · 05-21-2015, 03:09 AM

Hi.

First of all, I do not understand your MATCH construction here.

Secondly, the unqualified ROW(), as always, is a poor choice for generating the k parameter for SMALL: here, for example, since your first formula is in row 4, this part will also return 4, meaning that you will miss the 1st, 2nd and 3rd results.

ROWS is a far better choice in such situations, since it is both immune to row insertions within the range and, more importantly, is not dependent upon the row in which the formula resides. See here for more:

http://excelxor.com/2014/08/25/row-v...er-generation/

All in all, simply (in B4):

=IFERROR(INDEX($E:$E,SMALL(IF(ISNUMBER(SEARCH($B$1,$H$5:$H$129,1)),ROW($H$5:$H$129)),ROWS($1:1))),"")

Regards