Date Range Lookup Formula

denmarie84

New Member
Joined
Sep 10, 2015
Messages
5
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!!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Note: there is a blank column between column B (yr-type) and column D (beg date). My table didn't display very well!​
 
Upvote 0
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]
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top