 # Unable to perform the index and multiple match array function

• I am getting an error while running the formula on index and multiple match in sheet Run Pivots and cell W38of the 'Valuation of the Indian Premier League' excel file.

Could anyone help me with respect to the correction.

## Files

=INDEX(\$Q\$38:\$T\$296,MATCH(1,(V38=\$R\$38:\$R\$296)*(\$W\$37=\$Q\$38:\$Q\$296),0),MATCH(\$X\$37,\$Q\$37:\$T\$37,0))

Or a non array version

=INDEX(\$Q\$38:\$T\$296,MATCH(W\$37&"|"&V38,INDEX(Q\$38:Q\$296&"|"&\$R\$38:\$R\$296,0),0),MATCH(\$X\$37,\$Q\$37:\$T\$37,0))

=INDEX(\$Q\$38:\$T\$296,MATCH(1,(V38=\$R\$38:\$R\$296)*(\$W\$37=\$Q\$38:\$Q\$296),0),MATCH(\$X\$37,\$Q\$37:\$T\$37,0))

Or a non array version

=INDEX(\$Q\$38:\$T\$296,MATCH(W\$37&"|"&V38,INDEX(Q\$38:Q\$296&"|"&\$R\$38:\$R\$296,0),0),MATCH(\$X\$37,\$Q\$37:\$T\$37,0))

It worked. Thanks a lot. But I am unable to understand how this formula works. Could please explain in brief?

• Or.............

In cell W38, enter this simply formula and copied down :

=SUMIFS(S:S,Q:Q,W\$37,R:R,V38)

Regards

• You're welcome & thanks for the feedback

It worked. Thanks a lot. But I am unable to understand how this formula works. Could please explain in brief?

I simply corrected your formula, to take the the column search, out of the row search

=INDEX(\$Q\$38:\$T\$296,MATCH(1,(V38=\$R\$38:\$R\$296)*(\$W\$37=\$Q\$38:\$Q\$296),0),MATCH(\$X\$37,\$Q\$37:\$T\$37,0))

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!