How do I Vlookup with multiple results in the same cell

[Bassie]

Hey everyone,

I have a sheet with 500 values that I need to vlookup against 50k products. Normally that woudnt be a problem with vlookup but in this dataset one value can have multiple results and vlookup only takes the first one.

So my question is is there an easy way to get multiple vlookup results in the same cell without any duplicates if that is also possible. This can either be a complex formula or a VBA code.

Thanks a ton in advance
Bart

 

[Fluff]

Can you post some sample data along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Bassie]

Thanks!

I made a small sample of it down below

Book2
	A	B	C	D	E	F
1	Sheet 1	formula goes down here			Sheet 2
2	Value X	Vlookup column X with Y			Value X	Value Y
3	10	Green, Yellow			10	Green
4	11	Purple			10	Green
5	12	Blue			10	Green
6	13	Lightblue			10	Yellow
7	14	Pink, Red			11	Purple
8					12	Blue
9					13	Lightblue
10					14	Pink
11					14	Red
Sheet1

 

[Fluff]

Thanks for that.
How about

+Fluff 1.xlsm
	A	B
1	Value X	Value Y
2	10	Green
3	10	Green
4	10	Green
5	10	Yellow
6	11	Purple
7	12	Blue
8	13	Lightblue
9	14	Pink
10	14	Red
Sheet2

+Fluff 1.xlsm
	A	B
1	Value X	Vlookup column X with Y
2	10	Green, Yellow
3	11	Purple
4	12	Blue
5	13	Lightblue
6	14	Pink, Red
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=TEXTJOIN(", ",,UNIQUE(FILTER(Sheet2!$B$2:$B$10,Sheet2!$A$2:$A$10=A2)))

 

[TylerWhieler]

I like Fluff's formula,
here is what I personally would use