Return ALL ID#s from another sheet if other Sheet equals "specific name"

abogdan

Hello Everyone, I am brand new to using smart sheets. I have a lot of experience us PBI but not as familiar with the smartsheet formula writing. I have a completely blank sheet and also a roster sheet with ID numbers and location names. What I want to do is to return ALL ID numbers from the roster sheet row by row in one column IF the roster sheet matched a specific location name. I used INDEX MATCH but that only returns one value.

=INDEX({Employee ID}, MATCH("Alachua FL", {LOCATION}))

I tired this method but go an unparseable error

=INDEX(COLLECT({Employee ID}, {LOCATION} = "Alachua FL")))

Thank you in advance for any assistance.

Mike TV

@abogdan

Here's an example of one way of doing it. There may be a more "clean" version than my method.

Example sheet 1:

Example sheet 2 (with your formulas):

For the "Sarasota Employee ID #s" column you'll have to put a slightly different formula in each row:

=IFERROR(INDEX(COLLECT({Employee ID}, {Location}, ="Sarasota"), 1), "")

Where the 1 is at the end, you'll change that to a 2 on the next row, and so on for as many rows as you need. For the Denver column it'll be the same except for ="Denver" in the formula. If you go as far down on each row as the # of employees you think you'll have maximum at any one time, then you shouldn't need to tinker with adding more formulas after you get them all set up.