Excel tutorials

Displaying the Number of Row, if the Date is Between Two Dates in two columns in the same row

In this Excel tutorial, I will show how we can find out the Number of Row in the range of cells, where there is value as a condition between two values in two columns. In the picture below, see the Start and End Dates in 'A' and 'B' columns. Date as a condition is located in 'H1' cell. In 'H4' and 'H6' cells the results are shown, ie the Row Number in which the condition of the 'H1' cell is met. I want to immediately noted, the number of rows based on the 'A2:A4' range or 'B2:B4' range. In the 'A2' cell, the first row that the formula takes to the calculation starts, (So, A2=First Row, A3=Second Row, A4=Third Row).

In the 'H1' cell, row 2 is displayed as a result. Here's one condition. The Date from 'H1' cells is searched in 'A and B' columns. Since the required condition does not exist, the formula looks for the first smaller Date and as a result returns the Number of Row in which the Date was found.

In the 'H6' cell is also shown Row number 2. Here is also a condition. Unlike the previous case, here the requirement of the 'H1' cell is searched Between Two Dates in the same row in columns 'A' and 'B'. So, the formula found that the criteria date (7.10.2019) is between two dates (2.4.2019 and 10.10.2019) in the second row (Row number 2).

In the Image 1 in the commentary on the 'H6' cell, I wrote '2 Conditions'. Do not take this into account literally. Here are two columns.

Date format in Excel -> The date in Excel is actually a value (number). So the date is 7.10.2019 equivalent to number 43745.

Image 1.

Return the Row number if the condition between the two values is met

How to find out and display the Row Number in which the condition for the value Between Two Values in two columns is met

Solution to this task, ie how to find the Row Number of a date as a condition between two dates (searching for a value between two values).

Image 2.

Illustrative explanation of formulas that contain MATCH, MIN and IF Excel functions, to return a row number if the condition is met

Calculation of a formula that returns a row number if the value is between two values in the same row using the F9 key

The formula in 'H4' cell follows the below.

A pictorial explanation of this formula can be seen in Image 2 above. In this formula, the last MATCH function argument is important. As you can see in Image 2 of the formula, look for a date from 'H1' cells in the 'A' column. Since the third argument (match_type) is set to number 1, if the formula does not find the exact Date, then searches for the first lower Date of the condition (ie, the number value) and returns the Row number in which the relevant Date is found.

If you use the 'F9' key to see the formula calculation then you can see that in the second argument, the formula show all the numeric values for the Dates it found in the 'A2:A4' range. Since our Date as a condition is displayed by the number '43745' and it does not exist in the relevant cell range, then the formula found the first smaller Date and its numeric value is '43557'.
This number is in the second place in the order of the numeric values {43466;43557;43749}. Since the number is in the second position, the formula returns 2 as a result.

ARRAY Formula (CSE formula) in 'H6' cell follows below.

A pictorial explanation of this formula can also be seen in Image 2 above. In this formula we also have the Date as a condition and it is in the 'H1' cell. Unlike the previous case when a condition is required in one column, here the Date as a condition (Value as a condition) we search Between Two Dates, "Start Date and End Date in the same row" (between the two closest values in the same row). So the condition is required between dates in 'A and B' columns.

In this array 'MIN formula' we have a nested formula of IF function. The first argument of the IF function consists of two conditions. In the first argument (logical_test) we have an expression ($H$1>=$A$2:$A$4)*($H$1<=$B$2:$B$4). So, we're looking for Dates that are 'bigger-equal' or 'smaller-equal' to our Date condition from the 'H1' cell. If you use the 'F9' key to calculate then as a result you have the expression {0;1;0}. Notice the mixed number string '0' and '1'. We are interested in this number '1' that is in the second position in the order.

In the second argument IF function (value_if-true) we have the formula MATCH(ROW($A$2:$A$4);ROW($A$2:$A$4)). This formula returns as a result a series of numbers of row in the relevant range that we set in the formula. Its result in calculation using the 'F9' key is following {1;2;3}. Considering that in the first argument IF functions have the number '1' in the second position and here in this row in the second position is the number '2'. Thus, the formula is based on digits '1' from the first argument and returns the value or number from the second argument for the corresponding position. Remark! The third argument of the IF function is omitted.

I hope I have somehow managed to explain how these two formulas work, to return a row number based on a condition that is between two values.

Please note that the absolute and relative addresses in the formulas should be correctly set, especially if you copy the formula.

Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.