Lookup in previous year

M

Mo

Hello,

I am working on a report which has a sheet with a column per year, covering
several years. In a seperate sheet I have a column where I lookup the value
for the last month entered (defined as "actual month"), for example 'July
04'. In a second column, I'd like to lookup the value corresponding to the
same month but a year earlier, so 'July 03', is there a way of saying lookup
actual month "-12"?

Thanks a lot in advance for your help...!!!
 
F

Frank Kabel

Hi
how have you entered your dates. As real date values but just formated
as 'MMM YY'?
 
F

Frank Kabel

So they ae text values?.
In this case it is more difficult. any chance you can enter them as
real date. e.g.
01/04/2004
and THEN use 'Format - Cells' and a custom format like 'MMMM YY'. This
would be easier for the formulas
 
F

Frank Kabel

Hi
so lets assume the following:
- column A on sheet 1 stores these date values. You have entered them
in the format for example 01-07-2004 (1st July 2004)
- in column B is the associated value

Now if you want for example the value from the previous year you could
use
=VLOOKUP(DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),A1:B100,2,0)
 
M

Mo

Thanks a lot for this, it works...

Continuing to work on these reports, I have noticed one thing, this gives me
the value 12 months earlier compared to today's date. Is it possible to refer
to a - chosen month?

I will try to be more explicit.
- I have in one sheet a cell defined as "month" (where every month I change
to today's month once data available)
- in a second sheet I have all the data (one column per month for several
years)
- in a third sheet I have a table where on the first column I do a Hlookup
of the "month" for a e.g. row 8. One the second column, I would to have the
same information (so the same row) but 12 months before. Which means that for
example today, although we are in September, by entering July in the "month"
cell, it will give me the July 2004 data in the first column but also the
July 2003 data in the second colum. How can I do this?

Thanks a lot for everything...!!!
 
Top