# Thread: Return multiple values in one cell from VLOOKUP Thanks: 0 Likes: 0

1. ## Return multiple values in one cell from VLOOKUP

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-...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  Reply With Quote

2. ## Re: Return multiple values in one cell from VLOOKUP

=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.

 A1, A2 Red A1 Blue Red, Green A1 Green A2 Purple A2 Orange A3 Yellow A4 Black A5 White Lookup a1  Reply With Quote

3. ## Re: Return multiple values in one cell from VLOOKUP

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))  Reply With Quote

4. ## Re: Return multiple values in one cell from VLOOKUP

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.  Reply With Quote

5. ## Re: Return multiple values in one cell from VLOOKUP Originally Posted by 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))
HI Fluff,

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

Cheers

Phil  Reply With Quote

6. ## Re: Return multiple values in one cell from VLOOKUP

You're welcome & thanks for the feedback  Reply With Quote

## User Tag List

a1b8, cell, return, searched, vlookupb11 