VLookup between date ranges ?
 

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

VLookup between date ranges ?
 

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

VLookup between date ranges ?
 

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.

VLookup between date ranges ?
 

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))

VLookup between date ranges ?
 

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

VLookup between date ranges ?
 

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

VLookup between date ranges ?
 

Perfect. Thanks.

Steve

"Glenn" wrote:

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))
.