A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

VLookup between date ranges ?



 
 
Thread Tools Display Modes
  #1  
Old April 23rd, 2010, 05:06 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 2,662
Default 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  
Old April 23rd, 2010, 05:19 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 2,662
Default 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  
Old April 23rd, 2010, 05:27 PM posted to microsoft.public.excel.worksheet.functions
Glenn[_6_]
external usenet poster
 
Posts: 1,245
Default 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  
Old April 23rd, 2010, 05:32 PM posted to microsoft.public.excel.worksheet.functions
Glenn[_6_]
external usenet poster
 
Posts: 1,245
Default 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  
Old April 23rd, 2010, 06:09 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default 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  
Old April 23rd, 2010, 06:51 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 2,662
Default 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  
Old April 23rd, 2010, 06:51 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 2,662
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:18 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.