Index Match Formula with Multiple Criteria?
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 26 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

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....)
Answers

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

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

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
Categories
Check out the Formula Handbook template!