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.

abogdan

Hi @Mike TV , is there a way that the formula can automatically fill the numbering to continue the series of 1 then 2 then 3 instead of me having to go in and manipulate it and change the numbers manually? This is definitely the right direction to go but my real data has over two thousand employee IDs.