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>

 

[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

	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>

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.