If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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)) |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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)) . |
Thread Tools | |
Display Modes | |
|
|