# Pulling Multiple Rows of Data into 1 Cell

edited 05/18/20

Hello,

Does anyone know how to pull multiple rows of data into one cell?

I'd like to build a cell that look-up the date "05/20/20" and brings all the values into one cell.

I'm thinking vlookup:

=VLOOKUP("5/20/20", {Test Range 1}, 2, false) + " - " + VLOOKUP("5/20/20", {Test Range 1}, 3, false) + " - " + VLOOKUP("5/20/20", {Test Range 1}, 4, false) + " - " + VLOOKUP("5/20/20", {Test Range 1}, 5, false)

But that gives me only the first line:

"02:30 PM - 02:50 PM - Cats in love - Wiskeys"

Any way to also pull the data for so that the cell can read:

"

02:30 PM - 02:50 PM - Cats in love - Wiskeys

02:50 PM - 03:10 PM - Cat nip - Meowton

03:10 PM - 04:00 PM - Q&A - All

"

?

L

thank you!

I ended up doing a concatenation of cells horizontally with

=JOIN([Time Start]1:[Session Speaker]1, " - ")

and then doing a helper

"

-

-

"

and a

=JOIN(COLLECT({Test Range 1}, {Test Range 2}, [date]1), SUBSTITUTE(Helper\$1, "-", ""))

• ✭✭✭✭✭✭

You can just use join(collect())

thank you!

I ended up doing a concatenation of cells horizontally with

=JOIN([Time Start]1:[Session Speaker]1, " - ")

and then doing a helper

"

-

-

"

and a

=JOIN(COLLECT({Test Range 1}, {Test Range 2}, [date]1), SUBSTITUTE(Helper\$1, "-", ""))

• ✭✭✭✭✭✭

Yes. I was just going off of what you already had instead of the JOIN function in the helper column.

CHAR(10) is actually a line break, so you could use that instead of using the SUBSTITUTE and the helper.

Happy to help and glad you were able to get it working. 👍️

• Oh!!!!!!!!!! @Paul Newcome !!!!! You made my day!! thank you!!!!!!

• ✭✭✭

@Paul Newcome is absolutely brilliant