Return multiple values from one search criteria

Katie C · 03/11/24

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 · 03/13/24

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 · 03/12/24

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 · 03/12/24

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 · 03/13/24

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 · 03/14/24

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

Paul Newcome · 03/14/24

Happy to help. 👍️

Katie C · 03/14/24

@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 · 03/18/24

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

@cell = TODAY()

PeggyLang · 06/27/24

@Paul Newcome

Can you pull the multiple values and enter them on separate lines?

I have a scenario whereby I want to pull from my master data sheet all the store locations for a certain City. I would like store info I.e., store #, address, ST, phone #, etc. for each unique store # on a separate row. Some cities may have only 1 store, some 2 or 5, etc.

Paul Newcome · 06/28/24

@PeggyLang Yes. You would need the "Number" helper column that is pre-filled with the numbers 1 through however many you think you will need. Then you would use an INDEX/DISTINCT/COLLECT combo to get the stores

=IFERROR(INDEX(DISTINCT(COLLECT({Source Sheet Store # Column}, {Source Sheet City Column}, @cell = "City Name")), Number@row), "")

Then you would use a standard INDEX/MATCH to pull over the rest of the data based on the store number.

=IFERROR(INDEX({Source Sheet Address Column}, MATCH([Store #]@row, {Source Sheet Store # Column}, 0)), "")

PeggyLang · 06/28/24

@Paul Newcome
THANK YOU AGAIN!!!
I have a COUNTIFS to determine the max # of locations in 'City'; therefore I know the max # of locations in any one City = 8.
Questions;
- 'Number' helper column should be in the 'Lookup' (or target) worksheet? (I would have 8 rows in my current instance)
- 'IFERROR(INDEX(DISTINCT(COLLECT({Source Sheet Store # Column}, {Source Sheet City Column}, @cell = "City Name")), Number@row), "")'
- is '@cell' referencing a cell or should be entered verbatim as '@cell'
- 'Number@row' references the 'Number' helper column in the 'Lookup' worksheet?

Paul Newcome · 06/28/24

@PeggyLang

The [Number] column goes in the target sheet where you are creating your list, and @cell stays as is.

PeggyLang · 06/30/24

@Paul Newcome
I think I have interpretted your solution correctly. But am receiving 'Invalid Data Type'.

I'm obviously doing something incorrect?

PeggyLang · 07/01/24

@Paul Newcome
UPDATE - formula actually works - OF COURSE IT DOES - however, it does not seem to work for some cities, ie., Atlanta, Bradenton, Clearwater . . . I'm looking for others. The ones I have found it does not work for have multiple locations, but it does work for other cities where there are multiple locations.
This is most peculiar? Thoughts?

Paul Newcome · 07/01/24

Double check there are no spelling differences or hidden spaces anywhere. Are you able to provide a screenshot of some rows within the source data that should be pulling for Atlanta? DO you have any store numbers that start with leading zeros or have any letters in them?

PeggyLang · 07/01/24

@Paul Newcome
I have retyped 'Atlanta' and still did not work. I do have some store #'s with leading '0', however it seems to work elsewhere.

Source Sheet snippet below;

Lookup snippet below;

Return multiple values from one search criteria

Best Answer

Answers

Help Article Resources

Categories