Date Range Lookup Formula

[denmarie84]

Hi,

I have been trying to create a formula that will return a value from a table if it is within a range of dates. I have tried several different formulas based on answers to similar questions, but none seem to work for some reason. Here is a a summary of my table. I want to return the value from column F if the sample date falls between the beg date and the end date.
[TABLE="width: 500"]
<tbody>[TR]
[TD]
[TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl67, width: 82"]SampleDate[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 52"]
<tbody>[TR]
[TD="class: xl67, width: 52"]Yr-type[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] [/TD]
[TD]
[TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl67, width: 68"]Beg date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl67, width: 68"]End date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl67, width: 64"]Yr-type[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl67, width: 82"]1/4/1995[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl67, width: 68"]10/1/1994[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl67, width: 68"]9/30/1995[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl67, width: 82"]10/3/1996[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl67, width: 68"]10/1/1995[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl67, width: 68"]9/30/1996[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl67, width: 82"]9/30/1997[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl67, width: 68"]10/1/1996[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl67, width: 68"]9/30/1997[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl67, width: 82"]5/6/1998[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl67, width: 68"]10/1/1997[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl67, width: 68"]9/30/1998[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl67, width: 82"]12/1/1998[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl67, width: 68"]10/1/1998[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl67, width: 68"]9/30/1999[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Here are some examples of formulas I've tried:

=INDEX($F$2:$F$6,MATCH(1,IF(A2>=$D$2:$D$6,IF(A2<=$E$2:$E$6,1)),0))
[as an array - ctrl/shift/enter]
returns #n/a

=INDEX($F$2:$F$6, SUMPRODUCT(--(A2<=$D$2:$D$6), --(A2>=$E$2:$E$6), ROW(D2:D6)))
returns each cell of column F in order, regardless of the input date


Any help would be appreciated, thanks!!

 

[denmarie84]

Note: there is a blank column between column B (yr-type) and column D (beg date). My table didn't display very well!​

 

[Weazel]

as long the be Beg Date is sorted ascending than maybe....

B2=IF(A3="","",LOOKUP(A3,$D$3:$D$11,$F$3:$F$11))

I'm not really sure from your data whether you actually have blank rows or not

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]SampleDate[/TD]
[TD]Yr-type[/TD]
[TD][/TD]
[TD]Beg date[/TD]
[TD]End date[/TD]
[TD]Yr-type[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

1/4/1995​

[/TD]
[TD]A[/TD]
[TD][/TD]
[TD]

10/1/1994​

[/TD]
[TD]

9/30/1995​

[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

10/3/1996​

[/TD]
[TD]C[/TD]
[TD][/TD]
[TD]

10/1/1995​

[/TD]
[TD]

9/30/1996​

[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

9/30/1997​

[/TD]
[TD]C[/TD]
[TD][/TD]
[TD]

10/1/1996​

[/TD]
[TD]

9/30/1997​

[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

5/6/1998​

[/TD]
[TD]D[/TD]
[TD][/TD]
[TD]

10/1/1997​

[/TD]
[TD]

9/30/1998​

[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

12/1/1998​

[/TD]
[TD]E[/TD]
[TD][/TD]
[TD]

10/1/1998​

[/TD]
[TD]

9/30/1999​

[/TD]
[TD]E[/TD]
[/TR]
</tbody>[/TABLE]

 

[denmarie84]

Thanks! It is giving me a #N/A for some reason? I pasted =IF(A2="","",LOOKUP(A2,$D$2:$D$6,$F$2:$F$6)). It is the same as yours so I am not sure why it wouldn't work. There are no blank rows, but other than that it is the same.

 

[Weazel]

are you sure the Beg Dates are actual dates and not formatted as text?

you can try =IF(A2="","",LOOKUP(A2,$D$2:$D$6+0,$F$2:$F$6)) and see if that works

 

[denmarie84]

Changing the formatting didn't help, but adding the 0 did! Thank you so much! I don't quite understand why or how it works, but it appears to have done the trick. Why doesn't it require the end date anywhere in the formula?

Anyway, thank you for your help!

 

[Weazel]

the Beg Date is probably text. using the +0 converts them back to actual dates inside the formula.
because the lookup is using an approximate match it looks at the date thnn scans the range for the greatest match larger then the lookup value and goes back one which than returns the value in column F