Use of Index match

[ghstrdr60]

On the attached schedule I am want to use multiple variables as my search criteria (area and region on the summary sheet) to lookup and locate information on a second sheet (detail). I have tried using index match but keep generating an error. Not sure what I am doing wrong any assistance would be greatly appreciated.

[daffodil11]

Had your row headers been unique, you'd use =INDEX(Detail!D6:J12,MATCH(Summary!E8,Detail!D5:J5,0),MATCH(Summary!F8,Detail!C6:C12,0))...

..but they aren't. You're looking for the SUM of the combination of those items.

I prefer to use INDEX when dealing with strings, and SUMPRODUCT when dealing with values.

=SUMPRODUCT((Detail!$D$5:$J$5=$E8)*(Detail!$C$6:$C$12=$F8)*(Detail!$D$6:$J$12)) and copied down

[ghstrdr60]

Thanks,

I now have some hair left on my head.

[daffodil11]

That makes one of us.

[newdoverman]

If you try and use INDEX and MATCH to get the results, you will only get partial results as the first values are taken and in this case, there are many duplicates of the data.

SUMPRODUCT is the way to go.