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.

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>