Formula to show current and previous months data

**dandavis1** · 07-06-2012, 06:47 AM

Please see attached.

I am wanting to put formulas into cell O7 and P7. So at the moment O7 should be showing yellow and P7 should be showing red. However when it comes to filling in sep-01 (say as an example - green) i then want O7 to show green being the most current month and P7 to be showing yellow.

I have a formula for this when it comes to showing numbers - here is an example for current month -
=AVERAGE(OFFSET(C149,0,MATCH(-1,C149:N149,-1)-1,1,-1))

So thought it would be possible to do this for text too?

ANy help appreciated.

CHeers,

**royUK** · 07-06-2012, 08:08 AM

Are you actually working on 2001 data?

You might be able to use HLOOKUP

**dandavis1** · 07-06-2012, 08:57 AM

2001 was just an example. Unfortunatly if i was to delete "green" in k7, the current month then shows zero, when i would need it to be showing "yellow" and previous month should be showing "red". Thats why i wondered whether the formula i already have could be altered in some way?!

Thanks for your response!!

**Melvinrobb** · 07-06-2012, 09:19 AM

If you are able to arrange your data vertically, the following should work. It might be possible to do this without the helper column I added, but I thought this was a better idea.
Let me know if this works for you!

**royUK** · 07-06-2012, 02:02 PM

Originally Posted by dandavis1

2001 was just an example. Unfortunatly if i was to delete "green" in k7, the current month then shows zero, when i would need it to be showing "yellow" and previous month should be showing "red". Thats why i wondered whether the formula i already have could be altered in some way?!

Thanks for your response!!

My example needs you to enter the current month in the cells above the table, it then selects the selected month and the one prior to taht