Dynamic Index formula [SOLVED]

[Injinia]

Hi,


I have a drop down list with months Jan - Dec. The formula below returns the chosen month's figures (I only copied until March ie $BF$2=3). So essentially on one column, I am supposed to get the figures for the corresponding month chosen.


=IF($BF$2=1;INDEX($E$4:$E$355;MATCH((CONCATENATE($AM3;AN$2));$A$4:$A$355;0));IF($BF$2=2;INDEX($F$4:$F$355;MATCH((CONCATENATE($AM3;AN$2));$A$4:$A$355;0));IF($BF$2=3;INDEX($G$4:$G$355;MATCH((CONCATENATE($AM3;AN$2));$A$4:$A$355;0)))))


I kindly need help to shorten this formula so that I don't need to have "12 IF conditions" for the 12 months, and in addition a short explanation of how that formula works.


Regards,

Injinia

 

[Luke M]

Index can take both a row argument AND a column argument. Since BF2 is already giving a number, the whole thing can be condensed greatly down to:

=INDEX($E$4:$P$355,MATCH($AM3&AN$2,$A$4:$A$355,0),$BF2)

 

[Injinia]

Awesome. Thanks Luke!