# 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.

• How about

=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))

• How about

=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))

• ## royUKMar 13th 2020

Changed the title of the thread from “Unable to perform the inded and multiple match array function” to “Unable to perform the index and multiple match array function”.