If Statement Plus Index(Match()


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,



Best Answers


  • 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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!