FUNCTION INDEX + MATCH alternate solution

[ferfer20]

Dear, I hope you can help me with the following query. I am performing a search in the matrix located in the range A2:G481 to bring me the values corresponding to the columns F and G, according to the criteria of Age (B:B), Year ($I$2) and Percentage according to gender (Male [F:F] and Female [G:G]). In columns L, M and N I have already made a calculation as I am looking for it, applying the SUMIFS function and it has given result. Now I want to replicate the same calculation but this time using the INDEX and MATCH function, for which I have done it in columns Q, R and S. For the male percentage, the calculations return the expected result, but when I try to apply the same function this time for the female percentage, I get #REF!

I hope you can tell me where I am making the mistake or if a simpler procedure using INDEX and MATCH can be applied.

Thank you for your attention.

[hrlngrv]

Your R2 formula only works due to dumb luck.

R2:

=INDEX($F2:$G481,MATCH($I$2,$A$2:$A$481,0),MATCH($Q2,$B2:$B481,0),MATCH(R$1,$F$1:$G$1,0))

You're calling IF with 4 arguments, which is syntactically valid for multiple area ranges. However, the 1st argument is a single-area range, $F2:$G481. For column R, the 4th argument evaluates to 1, which is OK for the single-area 1st argument. The formula fails in S2 and below because the 4th argument evaluates to 2, but there's no 2nd area in the 1st argument range.

There's also the problem that the 3rd argument, MATCH($Q2,$B2:$B481,0), isn't a column index, and because the 2nd argument in the MATCH call isn't row-absolute. Purely fortuitously, it returns 1 for all cells in R2:R21, which is why the formulas in those cells don't return errors and return correct results for I2 = 1996.

If you really want to use INDEX+MATCH for this, you need to use

R2:

=INDEX($F$2:$G$481,MATCH(1,INDEX(($A$2:$A$481=$I$2)*($B$2:$B$481=$Q2),0),0),MATCH(R$1,$F$1:$G$1,0))

Copy R2, select R2:S21, paste.

That said, if the source data for Male and Female are in adjacent columns and the results would also be in adjacent columns in the same order by column, no need for a 2nd MATCH call. Instead,

R2:

=INDEX(F$2:F$481,MATCH(1,INDEX(($A$2:$A$481=$I$2)*($B$2:$B$481=$Q2),0),0))

Copy R2, select R2:S21, paste.

[ferfer20]

Dear hrlngvr, thank you very much for your valuable opinion and explanation, with this it is clearer to me where I was making the mistake, I appreciate your dedication resolving my doubts.

fraternal hug!