Vlookup (or other formula) to return multiple values in same cell

[Dani_LobP]

Hi all,

I'm struggling to make up some formula where I can populate a cell with multiple results based on a specific value.

Idea is that I have 1 big table, where I would lookup a value, and that value would show from 1 to X rows, and from that filter lets say, there are 2 columns id like to concatenate so in the end, formula brings up in same cell, different lines with concatenations found.

visual example below, hope it makes sense :D

Vlookup Value	Number	Name
3	669	example 1
2	1174	example 2
4	1476	example 3
4	1536	example 4
8	1546	example 5
10	1564	example 6

Values
2	formula to obtain ->	1174 example 2
3	formula to obtain ->	669 example 1
4	formula to obtain ->	1476 example 3 1536 example 4

So point is that i can populate 2nd table based on data from the first table...
Hopefully someone here saw something similar and can help me with it

As for now is a very manual task but im sure there are some formulas i can implement to make it automatic hehe

thanks in advance.

 

[offthelip]

this is easily done with index match. I put the value to lookup in G2 and assume the otehr colu9mns started in column A

=INDEX(B2:B7,MATCH(G2,A2:A7,0))& " " & INDEX(C2:C7,MATCH(G2,A2:A7,0))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Dani_LobP]

this is easily done with index match. I put the value to lookup in G2 and assume the otehr colu9mns started in column A

=INDEX(B2:B7,MATCH(G2,A2:A7,0))& " " & INDEX(C2:C7,MATCH(G2,A2:A7,0))

Thanks for the quick reply, I'm trying to implement this way... but struggling to identify my columns.

Lets say I have my columns in another sheet, where from the example, column A would be column A, column B would be column B and column C would be column E.
G2 is actually B2 in main tab where I try build the table.

So when I try place the formula above in the cell, I get #NA error..

=INDEX('Raw1'!B:B,MATCH(B2,'Raw1'!A:A,0))& " " & INDEX('Raw1'!E:E,MATCH(B2,'Raw1'!A:A,0))

Am I missing something? Column A in the Raw1 sheet is actually a formula. Does it impact this and I should remove the formula?

Thanks in advance.

 

[Dani_LobP]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

just updated thanks

I'm using excel 365 apps for enterprise. says version 2109 ..

 

[Fluff]

Thanks for updating your profile.
How about

+Fluff 1.xlsm
	A	B	C
1	Vlookup Value	Number	Name
2	3	669	example 1
3	2	1174	example 2
4	4	1476	example 3
5	4	1536	example 4
6	8	1546	example 5
7	10	1564	example 6
Sheet1

+Fluff 1.xlsm
	A	B
1	Values
2	2	1174 example 2
3	3	669 example 1
4	4	1476 example 3 1536 example 4
Sheet2

Cell Formulas
Range	Formula
B2:B4	B2	=TEXTJOIN(CHAR(10),,FILTER(Sheet1!$B$2:$B$7& " "&Sheet1!$C$2:$C$7,Sheet1!$A$2:$A$7=A2))

 

[Dani_LobP]

Thanks for updating your profile.
How about

+Fluff 1.xlsm
	A	B	C
1	Vlookup Value	Number	Name
2	3	669	example 1
3	2	1174	example 2
4	4	1476	example 3
5	4	1536	example 4
6	8	1546	example 5
7	10	1564	example 6
Sheet1

+Fluff 1.xlsm
	A	B
1	Values
2	2	1174 example 2
3	3	669 example 1
4	4	1476 example 3 1536 example 4
Sheet2

Cell Formulas
Range	Formula
B2:B4	B2	=TEXTJOIN(CHAR(10),,FILTER(Sheet1!$B$2:$B$7& " "&Sheet1!$C$2:$C$7,Sheet1!$A$2:$A$7=A2))

Thanks Fluff,

I'm trying to replicate it but in this case i get #value! error :/

sheet1 is Raw1 for me. Where i have data in cols A,B and E... col A is where Vlook up would be found, and col B and E concatenations...
B2 is where i have the value that is being looked up in Raw1 sheet.

=TEXTJOIN(CHAR(10),,FILTER('Raw1'!$B$2:$B$10000& " "&'Raw1'!$E2:$E10000,'Raw1'!$A$2:$A$10000=B2))

Same as with previous Index, not sure if im missing something when trying to implement it in my case.

thanks again

 

[Fluff]

Check that you don't have any #value errors on the raw1 sheet.

 

[Dani_LobP]

Check that you don't have any #value errors on the raw1 sheet.

I actually do... because that column is based on a formula.
So tried to add IFERROR at the start, and " " if error, but now the error in your formula is #Calc! .. i think i never saw that error before hehe

 

[Fluff]

At the end of the iferror use "" rather than ""
The #calc error means that the value in B2 could not be found in Col A, what sort of values are they?