Index Match Formula with Multiple Criteria?



I'm trying to create an index that has multiple criteria. I'm not sure if I should do this using an "IF" formula, or if I need to incorporate the "AND" function into the formula. Or if I need to go in another direction to accomplish this.

What I need to do is return the "Rate" based on the "Crew Type", and the "Number of People on the Crew". For example, a "Trim Standard Lift" Crew with 2 people has a different rate than a "Tree Trimming" Crew with 3 people. There are 8 different "Crew Types", and crews range 2-6 people.

This is an example of the data from each row. This sheet is where I'll be be adding the row to each formula.

This is an example of the sheet I am indexing that contains the rates.

Is anyone able to point me in the right direction of how to build this formula?

Thanks in advance!

Best Answer


  • Shelby
    Shelby ✭✭✭✭✭

    @Amie LaCharite

    Have you found a solution for the index match for multiple criteria? I desperately need this as well.

  • Amie LaCharite


    No, unfortunately we did not. We used the 'Join' function as suggested, then decided to go in a different direction all together.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!