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.

 

[Dani_LobP]

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?

just numbers... I'm using formula LEFT .. in order to extract first numbers from another column which has a number . and some text.

=IFERROR(LEFT(AA2,(FIND(".",AA2,1)-1)),"")

example of column AA format below...

402. Example blablablabla

 

[Fluff]

In that case they are text not numbers, try

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

 

[Dani_LobP]

In that case they are text not numbers, try

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

oh that worked! great! thanks .. hehe :D

Not sure if I understand how the B2&"" would work, but it does.. what's the logic behind that command?

Thanks again for your help

one last thing, would you know how to touch the formula so each concatenation is on different line in the same cell? as for now, it seems to just put them all as a bulk one after the other creating a big wall of text when there's more than 1-2... i can do the manual fix , but wonder if there's a way to avoid it

 

[Fluff]

what's the logic behind that command?

It simply converts a number into text.

would you know how to touch the formula so each concatenation is on different line in the same cell?

Just turn wrap text on.

 

[Dani_LobP]

It simply converts a number into text.


Just turn wrap text on.

Yeah, trying to play with wrap text on/off and making bigger cell, but seems to be a 1 long text and excel not wanting them to be split

 

[Dani_LobP]

ok now it works, my bad.
I thought the CHAR(10) meant the number of characters to get, and i changed it to 30.. when i turned it back to 10 it works.
so its not doing what i thought it does...
what is it for the CHAR(10) if i may ask?

And once again thanks a lot for your help mate

 

[Fluff]

Char(10) is the linefeed character.

And once again thanks a lot for your help mate

You're welcome & thanks for the feedback.