Return multiple values from a single cell using a named range and INDEX/MATCH

[sb002]

Hi there, First post here so I apologize if I breach any rules. I have scoured the internet and this forum and so far been unsuccessful in finding a solution to my problem. Admittedly it is likely that I don't know the correct way to ask the question. Hopefully someone here can help me with this problem.

I have an excel csv file with approximately 3000 lines of text (it is a download from our website). I am trying to extract every mention of a colour within our product names to allow me to provide a more accurate filter capability. I have been trying to use the following as an array to solve my problem: =IFERROR(INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A2)),0)),"")

This has proven successful at extracting a single colour from my named range, however if a cell has multiple colour values this will only select the first in the list. My named ranges appear as follows (This isn't all my colours but just giving an example):
[TABLE="width: 500"]
<tbody>[TR]
[TD]things:[/TD]
[TD]results:[/TD]
[/TR]
[TR]
[TD]red[/TD]
[TD]red,[/TD]
[/TR]
[TR]
[TD]green[/TD]
[TD]green,[/TD]
[/TR]
[TR]
[TD]blue[/TD]
[TD]blue,[/TD]
[/TR]
[TR]
[TD]black[/TD]
[TD]black,[/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD]orange,[/TD]
[/TR]
</tbody>[/TABLE]

An example of my cell data would be:

Blue sweater with red stripes
black pants with orange pockets
green socks
[...]

I am hoping to compile all of discovered colours in a single cell (but can work with them all ending up individually in adjacent cells) to the right of the searched cell so the above would look as follows:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Blue sweater with red stripes[/TD]
[TD]blue, red[/TD]
[/TR]
[TR]
[TD]black pants with orange pockets[/TD]
[TD]black, orange[/TD]
[/TR]
[TR]
[TD]green socks[/TD]
[TD]green[/TD]
[/TR]
</tbody>[/TABLE]


Any assistance anyone can provide would be greatly appreciated. As I have been pulling my hair out trying to get it to return multiple values.
Shaun

 

[XOR LX]

Hi,

If you have Excel 2016 with an Office 365 subscription then this is quite simple, as you then have access to the TEXTJOIN function, i.e.:

=TEXTJOIN(", ",1,IF(ISNUMBER(SEARCH(things,A2)),things,""))

If not, and without recourse to VBA, you would be better off putting the results within individual cells, e.g. with the following array formula**:

=IF(COLUMNS($A2:A2)>COUNT(SEARCH(things,$A2)),"",INDEX(things,SMALL(IF(ISNUMBER(SEARCH(things,$A2)),ROW(things)-MIN(ROW(things))+1),COLUMNS($A2:A2))))

and copied to the right until you get blanks. Note that the above also assumes that things refers to a vertical - and not horizontal - range.

Just as a final note, I presume that you are aware that both your formula and those I give above will return values from things without taking into consideration whether they appear as 'whole words' or not within the string being searched. For example, if A2 contained:

Bluebottles are admired for their colour.

then "blue, red" would still be the result.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[James006]

@ XOR LX,

As usual ... Brilliant Solution ... :wink:

Hope you are doing fine

Cheers

James

 

[XOR LX]

Cheers, James!

 

[sb002]

@XOR LX

Sorry for the delay have been caught up with work. That solution worked wonderfully, thank you so much for the quick response.

 

[XOR LX]

@XOR LX

Sorry for the delay have been caught up with work. That solution worked wonderfully, thank you so much for the quick response.

No worries! You're welcome!

Cheers

 

[István Hirsch]

Hi,

If you have Excel 2016 with an Office 365 subscription then this is quite simple, as you then have access to the TEXTJOIN function, i.e.:

=TEXTJOIN(", ",1,IF(ISNUMBER(SEARCH(things,A2)),things,""))

If not, and without recourse to VBA, you would be better off putting the results within individual cells, e.g. with the following array formula**:

=IF(COLUMNS($A2:A2)>COUNT(SEARCH(things,$A2)),"",INDEX(things,SMALL(IF(ISNUMBER(SEARCH(things,$A2)),ROW(things)-MIN(ROW(things))+1),COLUMNS($A2:A2))))

and copied to the right until you get blanks. Note that the above also assumes that things refers to a vertical - and not horizontal - range.

Just as a final note, I presume that you are aware that both your formula and those I give above will return values from things without taking into consideration whether they appear as 'whole words' or not within the string being searched. For example, if A2 contained:

Bluebottles are admired for their colour.

then "blue, red" would still be the result.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Does this little modification solve the "navyblue" problem?

=IF(COLUMNS($A2:A2)>COUNT(SEARCH(" "&things&" "," "&$A2&" ")),"",INDEX(things,SMALL(IF(ISNUMBER(SEARCH(" "&things&" "," "&$A2&" ")),ROW(things)-MIN(ROW(things))+1),COLUMNS($A2:A2))))