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.
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))
Don’t have an account yet? Register yourself now and be a part of our community!