Vlookup to return multiple values in same cell

[KimC2504]

Hi All,

Traditionally the vookup will return the first value it can match. I can return multiple values on different lines using an array formula but this is not useful.

In the example below I have table array A1:B8. Lookup criteria in cell A11 and want to return MULTIPLE values from column B in cell B11.

Any suggestions? I would prefer to not use VBA if possible

	A	B
1	A1	Red
2	A1	Blue
3	A1	Green
4	A2	Purple
5	A2	Orange
6	A3	Yellow
7	A4	Black
8	A5	White
9
10	Lookup
11	A1	Red, Blue, Green

<TBODY>
</TBODY>

 

[DRSteele]

I am thrilled to report that NOW there is a function that can do this. In the new version of Excel 2016 with the Feb updates, function TEXTJOIN gets it done:

	A	B
1	A1	Red
2	A1	Blue
3	A1	Green
4	A2	Purple
5	A2	Orange
6	A3	Yellow
7	A4	Black
8	A5	White
9
10	Lookup
11	A1	Red; Blue; Green

<tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula B11 {=TEXTJOIN("; ",1,REPT($B$1:$B$8,1*($A$1:$A$8=A$11)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[zerofirefox]

Could someone please attach a working excel file with the above formula of TEXTJOIN
I am starting to wonder is my Excel is functioning properly, maybe some update issue.
Cuz I can't get it to work

 

[Mike Szczesny]

Can you uses something like this? First in cell D1 place formula to isolate colors for A1. I put the =iferror(formula...." ") to get rid error messages after you get all your results. Then I use a concatenate transpose formula for end result. In DI I have

=IFERROR(INDEX($B$1:$B$8,SMALL(IF($A$1:$A$8=$A$11,ROW($B$1:$B$8)-ROW($B$1)+1),ROWS($D$1:D1)))," ") Use Cntrl+Shift+Enter I place my concatenation formula in B11. Before entering you need to highlight the transpose part of the formula with your mouse. Hit F9. Now remove the { and }. Hit enter. My formula in B8 is =concatenate(transpose(D1:D3&", ")). Do the F9 step, then enter

<tbody>
</tbody>

A1	Red		Red
A1	Blue		Blue
A1	Green		Green
A2	Purple
A2	Orange
A3	Yellow
A4	Black
A5	White
A1	Red, Blue, Green,

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[PCL]

Nop

 

[PCL]

time table in school having subject name, day and class in one cell,

Can you give an example

 

[PCL]

Jointext is an UDF no ? : Personnal function
From where you got it ??