I have the following two tables in MS Excel.

I want to count the number of occurrences of 'AB' in the rows of 'Adam' and in the columns of 'Feb' and place in P2 cell.

Index and Match commands used as follows returns 'AC'. What is the best way to solve this?

=INDEX($B$3:$L$12,MATCH($N$2,$A$3:$A$12,1),MATCH($P$1,$B$1:$L$1,1))

Here is the

CodePudding user response：

Have you tried using the SUMPRODUCT Function, it does not require CTRL SHIFT ENTER, so just copy the formula from here and paste it in the cell P2 and Fill down and fill across.

=SUMPRODUCT(($N$2=$A$3:$A$12)*($O2=$B$3:$L$12)*(P$1=$B$1:$L$1))