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
[TABLE="width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]A1
[/TD]
[TD]Red
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]A1
[/TD]
[TD]Blue
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]A1
[/TD]
[TD]Green
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]A2
[/TD]
[TD]Purple
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]A2
[/TD]
[TD]Orange
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]A3
[/TD]
[TD]Yellow
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]A4
[/TD]
[TD]Black
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]A5
[/TD]
[TD]White
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Lookup
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]A1
[/TD]
[TD]Red, Blue, Green
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[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
A1	Red
A1	Blue
A1	Green
A2	Purple
A2	Orange
A3	Yellow
A4	Black
A5	White
Lookup
A1

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="bgcolor: #C6E0B4"]Red; Blue; Green[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B11[/TH]
[TD="align: left"]{=TEXTJOIN("; ",1,REPT($B$1:$B$8,1*($A$1:$A$8=A$11)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[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 [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =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[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 311"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]A1[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]Blue[/TD]
[TD][/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]Green[/TD]
[TD][/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]Purple[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]Yellow[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]Black[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]White[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]Red, Blue, Green, [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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 ??