Additional layer to Index formula

Hi Forum,

Hoping you can help me with something I am currently stuck on. I am trying to automate a part of our spreadsheet with a calculator.

Purpose is to search associated rates with Job Titles and Function levels. This part of the formula currently works: =INDEX(Tool!$E$8:$G$23;MATCH(Calculator!$C$9;Tool!$D$8:$D$23;0);MATCH($C$10;Tool!E7:G7;0))

I want to add in an additional layer to the search by adding C8 : Hiring Type. Goal is to have the formula first look at the Hiring type, then the Job Title and then finaly the function level. No matter what I try, I am not able to add in this additional layer.

Hope someone can help me out! See attachement for source material.

Calculator


C11=SUMIFS(INDEX(Tool!E8:G100,,MATCH(Calculator!C10,Tool!E7:G7,0)),Tool!B8:B100,Calculator!C8,Tool!D8:D100,Calculator!C9)

Hi Sam, Caracalla, TMS,

Thank you very much for the proposed solutions and TMS big thanks for making the summary in the spreadsheet, much appreciated.


I went with =SUMPRODUCT((Tool!$B$8:$B$23=Calculator!$C$8)*(Tool!$D$8:$D$23=Calculator!$C$9)*(Tool!$E$7:$G$7=Calculator!$C$10);Tool!$E$8:$G$23) as one of the proposed solutions and it worked perfectly.

Thanks again and have a great week all!