Blue Flower

Return the Value from the Intersection if Two Conditions Match the Date and the Column Header

In this Excel tutorial, I will show how we can find value in a particular column, if the Date or Value is Between Two Dates or between two values in the same row. In the picture below you can see the situation of our task. In 'A1: E4' cell range, there are original data. In the column 'A' there is an initial date and in the 'B' column there is a final date. Also note the multiple columns that have the titles 'COL1, COL2 and COL3', in 'C2:E4' are the values we search and want to return as a result.

There is a first condition in 'I1' cell. This condition is a specific Date. We can also look at this Date as a Value, because we need to look at the Date Format in Excel. The Date in Excel is actually a value (number). So the date is 7.10.2019 equivalent to 43745.

In the 'I2' cell is another condition, representing the Column Header. Since we have multiple columns, we need to find the value that is in a particular column, but in the same row that the first condition between two dates has been met.

Find the value of a particular column, which is located in the same row if the date between the two conditions

 

Show the value if the value is between two values in two columns in the same row and if the column header condition is met

The solution to this task can be performed in at least two ways.
The first solution uses a simple INDEX/MATCH/MATCH formulas.
The second solution uses the INDEX function as a CSE formula where we have nested functions-formulas MIN/MATCH/ROW.

The formula in 'I4' cell follows the below.(If you use the 'F9' calculation key then you can notice the following expression of the formula =INDEX({5\4\2;50\70\90;100\200\300};2;2). Note that in the first the INDEX function argument shows the order of the values from the 'C2:E4' range. The second argument indicates the number 2 which is the number of row. In the third argument, the number 2 is displayed and this is the column number. So in this case, the INDEX formula returns the result of the second row and second column.

=INDEX($C$2:$E$4;MATCH(I1;$A$2:$A$4;1);MATCH(I2;$C$1:$E$1;0))

ARRAY Formula (CSE formula) in 'I6' cell follows the below. (This formula also returns a value based on the line number and column number. Unlike the previous formula, here is the second INDEX function argument used nested formulas MIN/IF/MATCH. By checking the formula calculation in one moment, we come to the following expression =INDEX({5\4\2;50\70\90;100\200\300};MIN(IF({0;1;0};{1;2;3}));2).

=INDEX($C$2:$E$4;MIN(IF(($I$1>=$A$2:$A$4)*($I$1<=$B$2:$B$4);MATCH(ROW($A$2:$A$4);ROW($A$2:$A$4))));MATCH($I$2;$C$1:$E$1;0))

Notice the similarity of this formula with the previous formula. If you want more detail and explanation of the formula for calculating and calculating the formula, then look at the previous excel tutorial in which I wrote about returning the Row Number if the condition is met.

I hope to somewhat to explain how these two formulas work to return value from the intersection of a given column if there are multiple number rows, 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.