Index Match Formula with Multiple Criteria?

Amie LaCharite

Hello,

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!

MCorbin

I usually create a formula column that joins the 2 columns I'm looking for in both sheets:

"Lookup"

=join([Crew Type]@row:[# of People on Crew]@row)

Then use your Lookup Column in the Index/Match formula as your lookup value

=Index({Rate},Match(Lookup@row,{Lookup},0)

(If other people access the sheet, I lock the Lookup Column and either hide it or move it to the far right....)

MCorbin

I usually create a formula column that joins the 2 columns I'm looking for in both sheets:

"Lookup"

=join([Crew Type]@row:[# of People on Crew]@row)

Then use your Lookup Column in the Index/Match formula as your lookup value

=Index({Rate},Match(Lookup@row,{Lookup},0)

(If other people access the sheet, I lock the Lookup Column and either hide it or move it to the far right....)

Shelby

@Amie LaCharite

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

Amie LaCharite

@Shelby

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