Formula for picking todays month and inputting cells to the right

[RuthRoss]

Hi,
Thank you for all your help so far. I am now trying to insert a formula that will check what month we are in now, and then insert the figures from the three cells to the right of the current month.

For example:

I have months over several years in column A. In Column B to D I have some figures that relate to that month.

In a separate area of the spreadsheet Cell G4, H4 and I4 - I want it to look up what month we are in, and then insert the right row from B into G4, C into H4, D into I4.

Is this possible to do? If so, what is the best route to take?

Thank you in advance for your help in this matter.

 

[T. Valko]

Sorry - not used the forum for some time, and can't quite remember how to do it all.

Your formula worked once I had corrected my mistake in F4. So thank you.

Ok, good deal. Thanks for the feedback!

 

[jasonb75]

Out of curiosity, is there any advantage to use TEXT on the lookup range instead of making the lookup value numeric?

=INDEX(B$2:B$6,MATCH(--($F4&YEAR(NOW())),$A$2:$A$6,0),0)

 

[T. Valko]

Out of curiosity, is there any advantage to use TEXT on the lookup range instead of making the lookup value numeric?

=INDEX(B$2:B$6,MATCH(--($F4&YEAR(NOW())),$A$2:$A$6,0),0)

No advantage, it's simply that we don't know what the real dates are in column A. They display as mmm-yy but they could be any date within the month.

That's why I chose to use the TEXT function.

There's ALWAYS a reason why I do something!

 

[jasonb75]

Good call, but we do know from the sample that they're in ascending order

=INDEX(B$2:B$6,MATCH(EOMONTH($F4&YEAR(NOW()),0),$A$2:$A$6),0)

but not everone has the analysis toolpak installed so you still come out on top