Return multiple values from one search criteria

Katie C

Hi!

I'm fairly new to Smartsheet formulas and have reviewed the community posts trying to find help to my question.

I am trying to pull PTO requests from one sheet to another. VLOOKUP has always been a friend, but unfortunately does not pull multiple values.

My lookup criteria is a single date, (ex 2/28/24) but there can be multiple PTO requests.

My main sheet looks like this for the results:

Can someone help with a formula?

TIA!

Paul Newcome

You need a JOIN/COLLECT combo to pull multiple values in based on a single match.

=JOIN(COLLECT({Source Sheet Column To Pull}, {Source Sheet Date Column}, @cell = DATE(2024, 03, 13)), "delimiter of choice")

AravindGP

Hi @Katie C

You can pull multiple values if you have a rough idea of how many you intend to pull. Let's say it is 5, you will need to add a column in your main sheet which pulls the data and have the numbers 1 to 5 written in each row. You can then use the INDEX/COLLECT formula.

=INDEX(COLLECT({Activity column from sheet 1}, {Date column from sheet 1}, DATE(2024,02,28)), [Column containing the numbers]@row)

You can replace the DATE function if you have a column in your main sheet containing the data to be used as your parameter.

Katie C

Hi,

Thank you so much @AravindGP for taking the time to help me. I got as far as creating the column with numbers and creating the formula. I'm getting an unparseable error though.

=INDEX(COLLECT({2024 PTO Calendar Range 2}, {2024 PTO Calendar Range 1}, DATE(Date1)),Categories35:Categories39@row)

Paul Newcome

You need a JOIN/COLLECT combo to pull multiple values in based on a single match.

=JOIN(COLLECT({Source Sheet Column To Pull}, {Source Sheet Date Column}, @cell = DATE(2024, 03, 13)), "delimiter of choice")

Katie C

@Paul Newcome This worked, thank you so much for helping out a newbie!!

Paul Newcome

Happy to help. 👍️

Katie C

@Paul Newcome sorry, one follow up- how can I use DATE to make sure my formula is grabbing today's date and data? Thanks!

Paul Newcome

You wouldn't use the DATE function at all. You would just use the TODAY function.

@cell = TODAY()