VLookup between date ranges ?

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

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
 
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
 
Steve said:
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
Click to expand...

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.
 
Steve said:
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
Click to expand...


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

Steve

Glenn said:
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))
.
Click to expand...
 
Back
Top