Return multiple values in one cell from VLOOKUP

[jcm900]

Hi,

I am using this formula

=CHOOSE(SUMPRODUCT(--(B11=A1:A8)),
VLOOKUP(B11,A1:B8,2,0),
VLOOKUP(B11,A1:B8,2,0) & ", " & INDEX(B1:B8,MATCH(B11,A1:A8,0)+1),
VLOOKUP(B11,A1:B8,2,0) & ", " & INDEX(B1:B8,MATCH(B11,A1:A8,0)+1) & ", " & INDEX(B1:B8,MATCH(B11,A1:A8,0)+2))


to return more than one value in a cell from VLOOKUP in relation to this thread

https://www.mrexcel.com/forum/excel-questions/647235-vlookup-return-multiple-values-same-cell.html


It works fine except that some of the cells that are being searched have more than one value themselves so I need to use a "contains" expression rather than just equals.

I have tried a wildcard "*"& B11 &"*" which returns a result if the searched cell "contains" the value of the refernce cell but I seem to have lost the abilty to return more than one value in a cell. Am I putting the wildcrd in the wrong place?

Many thanks

Phil

 

[jcm900]

I have added FALSE instead of "0" value as shown here

=CHOOSE(SUMPRODUCT(--(B11=A1:A8)),
VLOOKUP( "*"&B11&"*" ,A1:B8,2,FALSE),
VLOOKUP( "*"&B11&"*" ,A1:B8,2, FALSE ) & ", " & INDEX(B1:B8,MATCH(B11,A1:A8,0)+1),
VLOOKUP( "*"&B11&"*" ,A1:B8,2, FALSE ) & ", " & INDEX(B1:B8,MATCH(B11,A1:A8,0)+1) & ", " & INDEX(B1:B8,MATCH(B11,A1:A8,0)+2))

which gives an odd result woth some vakues returned correctly and some not. As you can see below the result should be red, blue, green but instead I only get two colours returned.


<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style><style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 286px"><col width="740"><col width="168"></colgroup><tbody>[TR]
[TD="bgcolor: #F2F6F8"]A1, A2[/TD]
[TD="bgcolor: #F2F6F8"]Red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #F2F6F8"]A1[/TD]
[TD="bgcolor: #F2F6F8"]Blue[/TD]
[TD]Red, Green[/TD]
[/TR]
[TR]
[TD="bgcolor: #F2F6F8"]A1[/TD]
[TD="bgcolor: #F2F6F8"]Green[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #F2F6F8"]A2[/TD]
[TD="bgcolor: #F2F6F8"]Purple[/TD]
[TD="bgcolor: #fafafa"][/TD]
[/TR]
[TR]
[TD="bgcolor: #F2F6F8"]A2[/TD]
[TD="bgcolor: #F2F6F8"]Orange[/TD]
[TD="bgcolor: #fafafa"][/TD]
[/TR]
[TR]
[TD="bgcolor: #F2F6F8"]A3[/TD]
[TD="bgcolor: #F2F6F8"]Yellow[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #F2F6F8"]A4[/TD]
[TD="bgcolor: #F2F6F8"]Black[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #F2F6F8"]A5[/TD]
[TD="bgcolor: #F2F6F8"]White[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #f2f6f8"][/TD]
[TD="bgcolor: #f2f6f8"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="bgcolor: #F2F6F8"]Lookup[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #F2F6F8"][/TD]
[TD="bgcolor: #F2F6F8"]a1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Hi & welcome to MrExcel.
Try
=CHOOSE(SUMPRODUCT(--(ISNUMBER(SEARCH(B11,A1:A8)))),
VLOOKUP("*"&B11&"*",A1:B8,2,FALSE),
VLOOKUP("*"&B11&"*",A1:B8,2,FALSE)&", "&INDEX(B1:B8,MATCH(B11,A1:A8,0)+1),
VLOOKUP("*"&B11&"*",A1:B8,2,FALSE)&", "&INDEX(B1:B8,MATCH(B11,A1:A8,0)+1)&", "&INDEX(B1:B8,MATCH(B11,A1:A8,0)+2))

 

[Fluff]

If you have the TEXTJOIN function you could use
=TEXTJOIN(", ",1,IF(ISNUMBER(SEARCH(B11,A1:A8)),B1:B8,""))

Confirmed with Ctrl Shift Enter, not just Enter.

 

[jcm900]

Hi & welcome to MrExcel.
Try
=CHOOSE(SUMPRODUCT(--(ISNUMBER(SEARCH(B11,A1:A8)))),
VLOOKUP("*"&B11&"*",A1:B8,2,FALSE),
VLOOKUP("*"&B11&"*",A1:B8,2,FALSE)&", "&INDEX(B1:B8,MATCH(B11,A1:A8,0)+1),
VLOOKUP("*"&B11&"*",A1:B8,2,FALSE)&", "&INDEX(B1:B8,MATCH(B11,A1:A8,0)+1)&", "&INDEX(B1:B8,MATCH(B11,A1:A8,0)+2))

HI Fluff,

Thank you. I now get Red, Green, Purple returned

Cheers

Phil

 

[Fluff]

You're welcome & thanks for the feedback