In this example, I have two datasets with different dates, and I want to line them up, using a formula, with the closest date, either before or afterwards.
Firstly, select all the dates in Dataset 1 and subtract the first date in Dataset 2.
=A3:A17–E3
This gets us the number of days between the dates in Dataset 1 and the first date in Dataset 2. To find the closest date, we need the smallest number in column C.
However, the closest date could be either before or after, which means the number could be positive or negative. To make all the numbers positive use the ABS function, which stands for absolute, this makes all the negative numbers, positive, and leaves all the positive numbers as they are.
=ABS(A3:A17–E3)
Then, we need to find the minimum number in column C, so we will use the MIN function.
=MIN(C3#)
Next, we need to find the position of this number in column C, and we will do this using the MATCH function, the lookup_value is the minimum number, the lookup_array is column C, and the match_type is 0 for an exact match.
=MATCH(G3,C3#,0)
This gives us the number 3, as the minimum number, 1, is in position 3 in column C.
To find the date and value in position 3, use the INDEX function. The array is all of Dataset 1, the row_num is the result from the MATCH formula, in this case 3, and as I want the results from all of the columns, I will put in a comma and leave the column_num blank.
=INDEX(A3:B17,H3,)
In this example, we get 15-Mar-2013 from Dataset 1, which is the closest date to 14-Mar-2013 in Dataset 2.
Finally, we need to combine all of these formulas together into one cell, and insert the dollar signs to make the cell references for Dataset 1, absolute cell references.
=INDEX($A$3:$B$17,MATCH(MIN(ABS($A$3:$A$17–D3)),ABS($A$3:$A$17–D3),0),)
Assuming you get all the dollar signs in the correct place, you will then be able to click and drag the formula down to fill in the rest of the column.
You will get the closest date for all of the other dates in Dataset 2.
