Using Index Match results in Error

[kittymate]

Hello,

I'm trying to use index match to return a lookup value. I want the formula to skip empty cells, and return the next filled cell while matching both row and column via index match.

problem.PNG

Currently, my formula to return Apple,c is:
=INDEX($B$2:$D$4,MATCH($A7,$A$2:$A$4,0), MATCH(D$6,$B$1:$D$1,0))

I have explored other formula like:
=INDEX(B2:D4,MATCH(1,(A2:A4=A7)*(B1:D1=D6)*(B2:D4<>""),0))
but it has resulted in error (#N/A).

Seeking help to match both row and column, while skipping empty cells. Thank you!

[josephteh]

Try this formula in B7:=SUMPRODUCT($B$2:$D$4*($A$2:$A$4=$A7)*($B$1:$D$1=B$6)), copy across and down.