Prev: Excel Formula for years and months
Next: VLookup between date ranges ?
From: Steve on 23 Apr 2010 12:06

A B
2010082 4/3/10
2010091 4/10/10
2010092 4/17/10
2010101 4/24/10
2010102 5/1/10
2010111 5/8/10

From: Glenn on 23 Apr 2010 12:27
Steve wrote:
> Sorry 'bout that. Somehow it got send too early.
> Here is my table representing A = Pay Period Week, B = Sat date of week begin.
> If I manually enter any date, e.g., 4/8/10, in I need the lookup to produce
> the A3, because 4/8 is in that week. 4/10 to produce A4, 4/16 to produce A5,
> etc.
> Something like if >= B3 but <b4, then A3.
>
> row A B
> 3 2010082 4/3/10
> 4 2010091 4/10/10
> 5 2010092 4/17/10
> 6 2010101 4/24/10
> 7 2010102 5/1/10
> 8 2010111 5/8/10
>
> Thanks,
>
> Steve
>
>

First, if you want to use VLOOKUP, put the dates in column A and the pay period
week in column B. Then, use the TRUE option for "range_lookup" in the VLOOKUP
formula. See the help file for details.
From: Glenn on 23 Apr 2010 12:32
Steve wrote:
> Sorry 'bout that. Somehow it got send too early.
> Here is my table representing A = Pay Period Week, B = Sat date of week begin.
> If I manually enter any date, e.g., 4/8/10, in I need the lookup to produce
> the A3, because 4/8 is in that week. 4/10 to produce A4, 4/16 to produce A5,
> etc.
> Something like if >= B3 but <b4, then A3.
>
> row A B
> 3 2010082 4/3/10
> 4 2010091 4/10/10
> 5 2010092 4/17/10
> 6 2010101 4/24/10
> 7 2010102 5/1/10
> 8 2010111 5/8/10
>
> Thanks,
>
> Steve
>
>


If you can't change the order of the columns to fit the requirements of VLOOKUP,
try this (with your date in D8):

=INDEX(A3:A8,MATCH(D8,B3:B8,1))
From: Jacob Skaria on 23 Apr 2010 13:09
Hi Steve

Try
with the lookupdate in c3

=LOOKUP(C3,B3:B8,A3:A8)

--
Jacob (MVP - Excel)


"Steve" wrote:

> Sorry 'bout that. Somehow it got send too early.
> Here is my table representing A = Pay Period Week, B = Sat date of week begin.
> If I manually enter any date, e.g., 4/8/10, in I need the lookup to produce
> the A3, because 4/8 is in that week. 4/10 to produce A4, 4/16 to produce A5,
> etc.
> Something like if >= B3 but <b4, then A3.
>
> row A B
> 3 2010082 4/3/10
> 4 2010091 4/10/10
> 5 2010092 4/17/10
> 6 2010101 4/24/10
> 7 2010102 5/1/10
> 8 2010111 5/8/10
>
> Thanks,
>
> Steve
>
>
From: Steve on 23 Apr 2010 13:51
Works great.

Thanks,

Steve

"Jacob Skaria" wrote:

> Hi Steve
>
> Try
> with the lookupdate in c3
>
> =LOOKUP(C3,B3:B8,A3:A8)
>
> --
> Jacob (MVP - Excel)
>
>
> "Steve" wrote:
>
> > Sorry 'bout that. Somehow it got send too early.
> > Here is my table representing A = Pay Period Week, B = Sat date of week begin.
> > If I manually enter any date, e.g., 4/8/10, in I need the lookup to produce
> > the A3, because 4/8 is in that week. 4/10 to produce A4, 4/16 to produce A5,
> > etc.
> > Something like if >= B3 but <b4, then A3.
> >
> > row A B
> > 3 2010082 4/3/10
> > 4 2010091 4/10/10
> > 5 2010092 4/17/10
> > 6 2010101 4/24/10
> > 7 2010102 5/1/10
> > 8 2010111 5/8/10
> >
> > Thanks,
> >
> > Steve
> >
> >
 |  Next  |  Last
Pages: 1 2
Prev: Excel Formula for years and months
Next: VLookup between date ranges ?