Index Match on 3 criteria

i am trying to get values from data after looking & matching on 3 criteria, i am trying with his formula but cant calculate as desired. There is something wrong in the formula which i couldn't find out.

=IFERROR((INDEX('rate sheet'!$C$2:$E$73,MATCH($A2,'rate sheet'!$A$2:$A$73,0)+MATCH($C2,INDEX('rate sheet'!$B$2:$B$73,MATCH($A2,'rate sheet'!$A$2:$A$73,0)):INDEX('rate sheet'!$B$2:$B$73,MATCH($A2,'rate sheet'!$A$2:$A$73)))-1,MATCH($B2,'rate sheet'!$C$1:$E$1,0))/60*D2),"0")

Kindly see the attached file & help me out.

thanks

Here, try this:

Formula:

Please Login or Register  to view this content.

You can add more critera in rate sheet 1st row to the right and down in first column and it will work.

Try ..

SUMPRODUCT(('rate sheet'!$C$2:$E$73)*('rate sheet'!$A$2:$A$73=$A2)*('rate sheet'!$B$2:$B$73=$C2)*('rate sheet'!$C$1:$E$1=$B2))/60*$D2

non-array formula

or (as per your posting)

=IFERROR((INDEX('rate sheet'!$C$2:$E$73,MATCH(1,('rate sheet'!$A$2:$A$73=$A2)*('rate sheet'!$B$2:$B$73=$C2),0),MATCH($B2,'rate sheet'!$C$1:$E$1,0))/60*$D2),"")

With Ctrl+Shift+Enter

Avoid using OFFSET as per zbor (Very inefficient - advice from the gurus .. not me!)

Originally Posted by JohnTopley

Avoid using OFFSET as per zbor (Very inefficient - advice from the gurus .. not me!)

True John.
On the other hand nothing slower than Sumproduct

We can't win!

In the end it really depends on the data volumes: if we don't get a "nanosecond" response we complain about the slowness of the calculations!