Index Match Multiple Criteria across sheets

Aaron Surber

I am trying to create a formula that matches criteria between two sheets, then populates a cell based on the criteria's results.

The goal is to use an index/match to populate the "fee" cell in the second picture based upon the client and service level.

Could someone explain how to create this formula? Thank you.

Paul Newcome

In that case, double check that each {Reference} is set up properly.

Paul Newcome

You would need an INDEX/COLLECT instead.

=INDEX(COLLECT({Fee}, {Client}, @cell = Client@row, {Service Level}, @cell = [Service Level]@row), 1)

Aaron Surber

Paul - Thanks for the information. I'm getting and unparsable response. Below is the formula I am using. The sheet I am indexing would reference the first sheet shown in the prior question. Am I indexing the incorrect sheet?

=INDEX({Client Service Level Fees Range 1}, COLLECT(({Fee}, {Client}, @cell = Client@row, {Service Level}, @cell = [Service Level]@row), 1))

Paul Newcome

Try following the same syntax I have in the formula I posted. How does that work?

Aaron Surber

It provided me with a #invalidref error.

Paul Newcome

In that case, double check that each {Reference} is set up properly.

Aaron Surber

Hey Paul - I am getting an incorrect argument at this point. Below is the formula I'm using:

=INDEX(COLLECT({Fee}, {Service Level Fee - Client}, @cell = Client@row, {Service Level Fee - Service Level}, @cell = [Service Level]@row, 1))

Aaron Surber

Found my error. Please disregard earlier message. Formula is working great. Thank you!

Paul Newcome

Happy to help. 👍️