Index and match function for Last Month figures

**Harrold** · 03-08-2011, 01:23 PM

Hi,

I tried to use index and match function to look for last month as shown attached (column M). But it works fine when there are two months, but when it comes to more than two months, the function failes.

Can someone here please advise how to rectify this issue?

Thanks in advance.

**Cheeky Charlie** · 03-08-2011, 01:53 PM

If you want the figure before the last 'actual':
=INDEX($A4:$K4,MATCH("actual",$B$1:$L$1,1))

**Harrold** · 03-08-2011, 08:34 PM

Hi Cheeky Charlie,

Thanks for your reply.

Based on your formula, it means i need to change the linking every month in order to capture the figure before the last 'actual'. I prefer to have it automation. Any suggestion?

Thanks again

**Cutter** · 03-08-2011, 08:57 PM

Try this:

=INDEX($A4:$L4,1,COUNTIF(A1:L1,"Actual"))

**Harrold** · 03-08-2011, 09:21 PM

Hi Cutter,

I tried but it gives me March figure instead of Feb.

Thanks

**Cutter** · 03-08-2011, 09:29 PM

I thought that was what you wanted.

Just subtract 1 from the COUNTIF() to get the second last actual.

=INDEX($A4:$L4,1,COUNTIF(A1:L1,"Actual")-1)

**Cutter** · 03-08-2011, 09:39 PM

To avoid error when looking for January you can use:

=INDEX($A4:$L4,1,MAX(1,COUNTIF($A$1:$L$1,"Actual")-1))

**Cheeky Charlie** · 03-09-2011, 04:24 AM

change the linking every month in order to capture the figure before the last 'actual'.

I don't understand. With the given formula each time you add 'actual' in row 1 you get the next value.

Automatic by date would look like:
=INDEX(A4:L4,MONTH(TODAY())-1)

Be specific about what you want.