=TODAY() and HLOOKUP - help needed

[GD]

Hi, I am building a model that I am trying to 'automate' as well as possible
to reduce running time of scenario work. The data I utilise is in monthly
buckets, so for example I have a January Forecast, February Forecast, March,
April, May etc etc etc

What I would like to do is have a HLOOKUP table with the various forecasts
in, that will drive the lookup cells to have the forecast in for THIS month..

So for example: ("March" being produced by an =TODAY or equivalent)

"March" | January February March April
May
3 | 1 2 3
4 5

So I have got a sheet in which I have an =TODAY formula, with the cell
formatted to mmmm-yy then the reference table with the dates in 01/01/2010,
01/02/2010, 01/03/2010 but formatted mmmm-yy as well - but for some reason
the Reference cell isn't picking anything up? Is this formatting or is there
another way round it?

The caveat, by the way, is my excel based optimisation tool won't operate a
linear solve with =IF statements controlling the data - so the solution can't
involve an IF

Any ideas?

 

[GD]

Eureka - I've got it working. I had to convert the output of an =TODAY cell
by doing the =TEXT formula in another, then running the reference of the
HLOOKUP to the name of that cell, brings the correct results.

Thanks for the help

GD

 

[Roger Govier]

Hi

Try
=INDEX(myrange,2,MONTH(TODAY()+1))
assuming myrange is something like B1:M2