Table of contents

  1. Match a date when a date range is entered in a single cell
  2. Match a date when a date range is entered in two cells
  3. Use VLOOKUP to search date in date ranges and return value on same row
  4. Match a date when date ranges sometimes overlap and return multiple results
  5. Functions in this post

Match a date when a date range is entered in a single cell

Column C contains the start and end date separated by a forward slash /. The formula in cell C9 splits the dates and checks if the date in cell C8 is in a date range, if it is it returns the adjacent value on the same row.

match date within date range

Array formula in C9:

=INDEX($B$3:$B$6, MIN(IF((DATEVALUE(RIGHT(C3:C6, LEN(C3:C6)-FIND("/", C3:C6)))>=C8)*(DATEVALUE(LEFT(C3:C6, FIND("/", C3:C6)-1))<=C8), MATCH(ROW($C$3:$C$6), ROW($C$3:$C$6)), "")))

Recommended article

Find date range

Elizabeth asks: Hi Oscar, Need help with a formula Please. I need to figure out the date range of a […]

Comments(5) Filed in category: Dates, Excel

How to return a value if lookup value is in range

Question: Hi, What type of formula could be used if you weren't using a date range and your data was […]

Comments(23) Filed in category: Excel, Lookup function, Vlookup

How to create an array formula

  1. Select cell C9
  2. Click in formula bar
  3. Copy and paste array formula to formula bar
  4. Press and hold CTRL + SHIFT
  5. Press Enter
  6. Release all keys

Recommended article

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Comments(2) Filed in category: Built-in features, Count values, Excel

Download excel file, see sheet Ex 1

matching-a-date-in-a-date-range v3.xlsx
(Excel 2007- Workbook *.xlsx)

Back to top

Match a date when a date range is entered in two cells

Array formula in cell C9:

=INDEX($B$3:$B$6, MIN(IF(($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6), MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6)))))

Recommended article

Find date given day and week

La Thăng asks: I want to find day if given date and week, for example : if given Tuesday, 32th […]

Comments(0) Filed in category: Dates, Excel

How to enter an array formula

  1. Select cell C9
  2. Paste formula
  3. Press and hold Ctrl + Shift
  4. Press Enter

Explaining array formula in cell C9

Step 1 - Check if date is in any of the date ranges

($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6)

becomes

(39994>={39814;39904;39995;40087})*(39994<={39903;39994;40086;40178})

becomes

{TRUE;TRUE;FALSE;FALSE}*{FALSE;TRUE;TRUE;TRUE}

and returns

{0;1;0;0}

Step 2 - Return row number if a range is found

IF(($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6), MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6)))

becomes

IF({0;1;0;0}, MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6)))

becomes

IF({0;1;0;0}, {1;2;3;4})

and returns {FALSE;2;FALSE;FALSE}

Step 3 - Return smallest value in array, ignore text and boolean values

MIN(IF(($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6), MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6))))

becomes

MIN({FALSE;2;FALSE;FALSE})

nad returns 2.

Step 4 - Return value from cell range

INDEX($B$3:$B$6, MIN(IF(($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6), MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6)))))

becomes

INDEX($B$3:$B$6, 2)

becomes

INDEX({"A";"B";"C";"D"}, 2)

and returns B in cell C9.

Download excel file, see sheet Ex 2

matching-a-date-in-a-date-range v3.xlsx
(Excel 2007- Workbook *.xlsx)

Back to top

Use VLOOKUP to search date in date ranges and return value on same row

The following formula uses only the VLOOKUP function, however the dates must be sorted ascending and if a date is outside date ranges 0 (zero) is returned. There can't be any overlapping date ranges and the formula can only return one value.

Formula in cell C13:

=VLOOKUP(C12,B3:C10,2,TRUE)

Recommended article

How to return a value if lookup value is in range

Question: Hi, What type of formula could be used if you weren't using a date range and your data was […]

Comments(23) Filed in category: Excel, Lookup function, Vlookup

Download excel file, see sheet Ex 4

matching-a-date-in-a-date-range v3.xlsx
(Excel 2007- Workbook *.xlsx)

Back to top

Match a date when date ranges sometimes overlap and return multiple results

Array formula in cell C9:

=INDEX($B$3:$B$6, SMALL(IF(($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6), MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6))), ROW(A1)))

Recommended article

Find overlapping date ranges in excel

Table of Contents Find overlapping date ranges Find overlapping date ranges with criterion Find overlapping date ranges Formula in cell […]

Comments(24) Filed in category: Excel, Overlapping

How to enter an array formula

  1. Select cell C9
  2. Paste formula
  3. Press and hold Ctrl + Shift
  4. Press Enter

How to copy array formula

  1. Select cell C9
  2. Copy cell (not formula)
  3. Select cell range C10:C11
  4. Paste

Download excel file, see sheet Ex 3

matching-a-date-in-a-date-range v3.xlsx
(Excel 2007- Workbook *.xlsx)

Back to top

Functions in this article:

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays

FIND(find_text,within_text,[start_num])
Returns the starting position of one text string within another text string. FIND is case-sensitive

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

LEFT(text,num_chars)
Returns the specified number of characters from the start of textstring

RIGHT(text,num_chars)
Returns the specified number of characters from the end of textstring

ROW(reference)
Returns the rownumber of a reference

LEN(text)
Returns the number of characters in a text string

DATEVALUE(date_text)
Converts a date in the form of text to a number that represents the date in Microsoft Office Excel date-time code

Back to top