Index Max Formulla #ref

[lukestkd]

Hi,
I have an index match formula returning the value from a range based on a criteria for Row 1 and another criteria for Column A:

=INDEX($B$2:$AH$5,MATCH($AL1,$B$1:$AH$1),MATCH($AK$1,$A$2:$A$5,0))

However this is returning a #ref ! error.

HOWEVER, if I reduce the number of columns in the match from 33 to 30 then the formula works.

So this works:

=INDEX($B$2:$AE$5,MATCH($AL1,$B$1:$AE$1),MATCH($AK$1,$A$2:$A$5,0))

Is there a way around this?

 

[steve the fish]

Sure its rows first then columns:

=INDEX($B$2:$AH$5,MATCH($AK$1,$A$2:$A$5,0),MATCH($AL1,$B$1:$AH$1,0))

You had columns then rows.