Hello Everyone,
i'm using an INDEX(SMALL()) array formula to search for multiple rows in a list which match a cell's value.
It works perfectly for exact values, but when i use wildcard characters i'm not having any luck, can someone please help.
example below:
The above works correctly if in the yellow box i enter the EXACT string from the data source.
However, if i enter "1ABC123" the 'Code 1 matches' section will only return row 1 of the data source.
My problem is, if i enter a string "1ABC123", i want it to show all rows which contain any part of the search string.
so if i enter a string "1ABC123", row 1 AND row 3 of the datasource will show up in the "Code 1 matches' section.
please note: all the formulas i've used are array formulas
i've tried using wildcard characters, and a few different formulas, but havnt had any luck.
if anyone knows how to do this, that would be epic.
thank you
i'm using an INDEX(SMALL()) array formula to search for multiple rows in a list which match a cell's value.
It works perfectly for exact values, but when i use wildcard characters i'm not having any luck, can someone please help.
example below:
The above works correctly if in the yellow box i enter the EXACT string from the data source.
However, if i enter "1ABC123" the 'Code 1 matches' section will only return row 1 of the data source.
My problem is, if i enter a string "1ABC123", i want it to show all rows which contain any part of the search string.
so if i enter a string "1ABC123", row 1 AND row 3 of the datasource will show up in the "Code 1 matches' section.
please note: all the formulas i've used are array formulas
i've tried using wildcard characters, and a few different formulas, but havnt had any luck.
if anyone knows how to do this, that would be epic.
thank you
