Lookup a value if the dates fall between two dates

excel date lookup between
user3084662 picture user3084662 · Mar 6, 2014 · Viewed 89.4k times · Source

I have window dates in two cells. For example:

  • a1 = 1/1/2014 8:00
  • b1 = 1/4/2014 10:00
  • c1 = 11

Where

  • A is the start date
  • B is the end date
  • C is the code for the window

In cell d1 I have the date: 1/3/2014 7:00

I would like to look up the date in a/b and if d falls between those dates then I would like for it to return the code for the window.

Answer

barry houdini picture barry houdini · Mar 6, 2014

Assuming you have multiple "windows" listed in order with no gaps perhaps try LOOKUP like

=LOOKUP(D1,A1:C10)

see example

Related questions

Count number of times a date occurs and make a graph out of it

I have a list of dates, each date in it can occur more than once. I want to count the number of times each date occurs (histogram) and display it in a graph (with the Y axis being the number …

Read More
Formula to convert date to number

I would like to know the formula to convert a date in 10/26/2013 to 41573 number as done in Excel. Like how 10/26/2013 is converted to 41573.

Read More
Ordering issue with date values when creating pivot tables

I have a 2-column spreadsheet which contains Dates and Sales figures as follows: When doing Right click -> Format Cells the values in the Dates column properly appear as Date (in the M/D/YYYY format). This applies to …

Read More