Excel help with index and match functions

kwyjibo · 10 Jul 2012 at 12:27

I'm not sure if I can get Excel to do this but what I want to do in the above image is to get the last values of each month automatically, the ones in the green, blue and red boxes.

I can already get the very last value in the B column using =INDEX(B:B,MATCH(9.99999999999999E+307,B:B))

Can this be modified to allow only for certain criteria?

Cheers

Sin_Chase · 10 Jul 2012 at 13:26

Macro/Script time for you

Morfik · 10 Jul 2012 at 13:34

=INDEX(A:B,(COUNTIF(A:A,E2)+MATCH(E2,A:A,0)-1),2)

Where E2 is your month name, eg May, June

kwyjibo · 10 Jul 2012 at 14:23

Morfik said:
=INDEX(A:B,(COUNTIF(A:A,E2)+MATCH(E2,A:A,0)-1),2)

Where E2 is your month name, eg May, June

Works perfectly, thank you