Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Location
    Detroit, Michigan, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    lookup with date ranges (2000)

    I want to lookup a date, my lookup table has date ranges and I want to return the value if my date is between the specified date range, inclusive.

    For example... I have July 1, 2004 as my date...I pass it to my lookup table where I have the following:

    6/1/2004 6/2004 168
    7/1/2004 7/31/2004 176
    8/1/2004 8/31/2004 180

    The result should be 176, since the date is between 7/1/2004 and 7/31/2004

    How can I set my lookup to do this. If the date is not in my lookup table, I don't mind the result be #NA. I just can't seem to find the formula to do this. I am familiar with VLOOKUP, but not where I fit in between a date range.

    Thanks for any help you can provide.

    TB

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: lookup with date ranges (2000)

    Like this?

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>1</td><td align=right>7/5/2004</td><td align=right>6/1/2004</td><td align=right>6/30/2004</td><td align=right>168</td><td>=VLOOKUP(A1,$B$1:$D$3,3,1)</td><tr><td align=center>2</td><td align=right>
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Location
    Detroit, Michigan, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lookup with date ranges (2000)

    John, thanks for the update, 1 question I have...the format of your vlookup I understand up until the (.........$d$3,3,1)? What is the 3,1 doing, if you don't mind explaining...thanks much.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: lookup with date ranges (2000)

    The 3 tells VLOOKUP to return the match in the 3rd column of the table. The 1 is actually unnecessary since your date range is sorted, it's the default if not set. It setts the optional 'range lookup' parameter of VLOOKUP, in this case "an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned". It's my habit to always set the parameter, according to whether the table data is sorted. To confuse you further, I use the shortcut of using 1 for TRUE and 0 for FALSE If this doesn't make sense, see the Help for VLOOKUP. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •