help with index/match with multiple criterias alongwith dates !

[VICTOR5]

hi to all experts,,,

here I am trying to develop a formula with the help of Index & match that criteria of Sheet matches with the criteria of sheet Data as follows :

I want a formula at sheet name "Result" at cell "B5" if the "B3", "B4" & "A5" matches the value of Data sheet then I should get result as 5 and follows the other cells too.

to define this formula I tried an array with index but could not work it seems.

=+INDEX(Data!$C$3:$R$20,MATCH(1,(Result!B4=Data!$B$3:$B$20)*(Result!B3=Data!$A$3:$A$20)*(Result!A5=Data!$C$2:$R$2),0))

for reference i attached the example sheet.

thanks in advance.....

[63falcondude]

Here's one option:

B5 =SUMPRODUCT((Data!$A$3:$A$20=B$3)*(Data!$B$3:$B$20=B$4)*(Data!$C$2:$R$2=$A5)*(Data!$C$3:$R$20))

or

=INDEX(Data!$C$3:$R$20,INDEX(MATCH(B$3&B$4,Data!$A$3:$A$20&Data!$B$3:$B$20,0),0),MATCH($A5,Data!$C$2:$R$2,0))

[CK76]

You can try...
=INDEX(Data!$C$3:$R$20,MATCH(B$3&B$4,Data!$A$3:$A$20&Data!$B$3:$B$20,0),MATCH($A5,Data!$C$2:$R$2,0))

Confirmed as array (CTRL + SHIFT + ENTER)

EDIT: Alternately below non-array method.
=INDEX(INDEX(Data!$C$3:$R$20,,MATCH($A5,Data!$C$2:$R$2,0)),AGGREGATE(15,6,ROW($3:$20)/(Data!$A$3:$A$20=B$3)/(Data!$B$3:$B$20=B$4),1)-2)

[VICTOR5]

Thanks guys.......its working.


i really appreciate

[63falcondude]

You're welcome. Glad we could help.