If Statement Plus Index(Match()

Joseph Noel

Hello Again Smartsheet Community,

Hoping to get some help with this one.

The project I am working on is essentially a group of people will submit a request, based on the submitter they will be assigned to Group 1, 2 or 3. Depending on the group there will be one of several people available to process their request. The processors will turn their availability on/off via another sheet.

Looking for an index match formula that will assign a processor from a particular group that is available when a submission comes in.

This is the intake sheet. The Group lookup does an index match from a master sheet based on the submitter.

The list of processors looks like this, where the checkbox means that worker is available, and that is populated from another simple sheet that the works can just check or uncheck if they are available.

So in layman's terms, I want a new submission to come in from a submitter, the sheet looks up what group that submitter belongs to (which i already have), and then go down the list of processors for that group and return the next available processor. My current formula is below, which doesn't work, but I feel like some combination of index/match/If and possible and statement could work.

=INDEX({Work Groups Range 1}, IF({Work Groups Range 3 =1}, MATCH([Group Look Up]@row, {Work Groups Range 2}, -1), "No One Available)))

Thanks everyone,

Joe

David Joyeuse

Hi @Joseph Noel

Your IF function have to be the first Function in your case. And you cannot have a range in a logical expression. If I got you well, you're looking for something like that in your [Next Available Worker] column:

=IF(COUNTIFS({Group Number Range}, [Group Look Up]@row, {Is Worker Available}, 1)>0, INDEX(COLLECT({First Worker Range}, {Group Number Range}, [Group Look Up]@row, {Is Worker Available}, 1),1), "No One Available")

This should return the first name of the group, where the box is checked.

Now you just have to make sure your guys are checking the box correctly :)

Hope it helped!

David Joyeuse

@Joseph Noel

As far as I know, no, I don't think it would be possible through Smartsheet only.

That might be possible through an API though, collecting everyone available, then using C or Java to generate a random name out of this list, then paste that name back in Smartsheet.

David Joyeuse

Hi @Joseph Noel

Your IF function have to be the first Function in your case. And you cannot have a range in a logical expression. If I got you well, you're looking for something like that in your [Next Available Worker] column:

=IF(COUNTIFS({Group Number Range}, [Group Look Up]@row, {Is Worker Available}, 1)>0, INDEX(COLLECT({First Worker Range}, {Group Number Range}, [Group Look Up]@row, {Is Worker Available}, 1),1), "No One Available")

This should return the first name of the group, where the box is checked.

Now you just have to make sure your guys are checking the box correctly :)

Hope it helped!

Joseph Noel

Thank you David, perfect formula worked the first time.

Follow up question, if instead of selecting the first person available I wanted to select a random person within that group that is available, would that be possible?

I notice there is no Random( function in Smartsheet.

Thanks again for your help, I was nowhere close.

David Joyeuse

@Joseph Noel

As far as I know, no, I don't think it would be possible through Smartsheet only.

That might be possible through an API though, collecting everyone available, then using C or Java to generate a random name out of this list, then paste that name back in Smartsheet.