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]

 

[MisterBates]

Have you seen this thread: http://www.mrexcel.com/forum/showth...rn-multiple-values-in-one-cell-(Concatenate-)

 

[Aladin Akyurek]

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]

There is no native function which can concatenate multiple values in a single cell. You could either get the results in a horizontal multi-cell record or invoke a function written in VBA which constructs a single cell result. Which would you like to have?

 

[KimC2504]

Have you seen this thread: http://www.mrexcel.com/forum/showth...rn-multiple-values-in-one-cell-(Concatenate-)

Thank you MisterBates, that thread was perfect and worked so well.

 

[KimC2504]

Hi Aladin, I was going to use a horizontal multi-cell record until I received the wonderful VBA function. I am so happy with it

 

[pplstuff]

Why not...

=vlookup()&", "&vlookup()&", "&...

Or build in some error handling if you want a cleaner look...

=iferror(vlookup(1),"")&if(isna(vlookup(1)),"",", "&vlookup(2))&if(or(isna(vlookup(1)),isna(vlooup(2))),"",", "&vlookup(3))

Something along those lines.

 

[ewilsonlds]

This would work as long as the number of colors returned never exceeded three:

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

 

[talent_anand]

Dear Sir,

I have an excel file as below

B
1 English - Nishant (1-3), Hindi Anand (4-6)
2 Hindi - Anand (4-6), English (1-3)


and so on. above data is in B1 AND B2

nOW i WANT as if I search for "Anand" and it returns me value, say in D1 AS English - Nishant (1-3), Hindi Anand (4-6), Hindi - Anand (4-6), English (1-3).

all cell containing string "Anand" in a column B. must be combined in cell D1.

 

[Aladin Akyurek]

@talent_anand

See whether this helps: http://www.mrexcel.com/forum/excel-questions/842384-date-occurs-within-date-range.html?

 

[talent_anand]

@talent_anand

See whether this helps: http://www.mrexcel.com/forum/excel-questions/842384-date-occurs-within-date-range.html?

Dear Sir

Thanks for your prompt reply,
Actually I have a time table in school having subject name, day and class in one cell, In a single column say Period 1, there may be more than one occurance of that particular teacher. so I need a forumula that as I put name of teacher in cell and it returns me his time table according to periods 1 - 8.