Re: Lookup in previous year

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 08/18/04 

Date: Wed, 18 Aug 2004 17:35:58 +0200

Hi
so lets assume the following:
- column A on *** 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) 

--
Regards
Frank Kabel
Frankfurt, Germany
"Mo" <Mo@discussions.microsoft.com> schrieb im Newsbeitrag
news:97358000-47FC-4C46-B969-4FC9E2633D55@microsoft.com...
> Hi again...
>
> any idea of what I should do now...?
>
>
>
> "Mo" wrote:
>
> > no problem, I have made the change you explained...
> >
> > "Frank Kabel" wrote:
> >
> > > 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
> > >
> > > --
> > > Regards
> > > Frank Kabel
> > > Frankfurt, Germany
> > >
> > > "Mo" <Mo@discussions.microsoft.com> schrieb im Newsbeitrag
> > > news:74CBD902-82E7-45C9-ABBE-000DC0A88E9E@microsoft.com...
> > > > yes exactly, I have entered them as "mmm yy"...
> > > >
> > > > "Frank Kabel" wrote:
> > > >
> > > > > Hi
> > > > > how have you entered your dates. As real date values but just
> > > formated
> > > > > as 'MMM YY'?
> > > > >
> > > > > --
> > > > > Regards
> > > > > Frank Kabel
> > > > > Frankfurt, Germany
> > > > >
> > > > > "Mo" <Mo@discussions.microsoft.com> schrieb im Newsbeitrag
> > > > > news:81661C54-E066-41BD-84E2-2E064466CC92@microsoft.com...
> > > > > > Hello,
> > > > > >
> > > > > > I am working on a report which has a *** with a column
per
> > > year,
> > > > > covering
> > > > > > several years. In a seperate *** 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...!!!
> > > > >
> > > > >
> > >
> > >
Quantcast