How to Concatenate multiple cells into a single cell within a VLOOKUP formula?

CmptrDan

I've created a VLOOKUP formula to pull in data from a source smartsheet, into a 'staging' smartsheet but, if more than one row match the criteria, only the first row entered in the source smartsheet get entered into the cell on the 'staging' smartsheet.

For instance, I'm looking for entries on the source smartsheet that meet a specific 'week-ending date' but, if the source smartsheet has multiple rows meeting the 'week-ending date' criteria, only the first row entered will be pulled into the 'staging' smartsheet. In my case, I would like to have the contents of all cells matching the criteria pulled into the single cell on the 'staging' smartsheet. I've looked at the JOIN and "+" functionality but can't figure out if/how to incorporate them in the VLOOKUP formula, since the content of the multiple rows in the source smartsheet is variable.

Can anyone help?

Thanks!

Dan.

RossL

can you post an image of what is going on? there could be a couple of different options here depending on the desired information.

CmptrDan

@RossL,  Attaching screen shot of the 'source' smartsheet containing two rows of data matching the criteria of the VLOOKUP formula, along with a screen shot of the 'staging' smartsheet, where only the first entry is pulled into each cell (vs. the two rows)

Paul Newcome

Try using a JOIN/COLLECT formula...

 

=JOIN(COLLECT({Range of Data you want to Join}, {Date Range}, [Week-Ending Date]23), " + ")

CmptrDan

Good morning @Paul Newcome,

This is exactly what I was looking for.

Thank you for this 'pearl of wisdom'!!!

Dan.

Paul Newcome

Happy to help!