Select the closest date

[jarrholm]

I am trying to search for the closest date value within a table with a couple of other conditionals. The logic is as follows:

1. If Type = I
2. Then Search the rest of the Number column for exact matching numbers
3. Only look at Type = O
4. Select the date that is closest (selected date should also be greater than what's in column C) as the end result

Below is what I was attempting and what I'm getting. It seems as though the pattern of the result is correct but something in the formula is not quite right. The result that I want is in column F. Any assistance would be much appreciated! I'm not even sure using the match function is the best option here.

One additional note. I tried converting column D to a short date and that gives the wrong dates.

 

[Oaktree]

Welcome to the board!

Your profile says you're using Office 2013. If that happens to be outdated and you're on 2019 or Office 365, you can use the new MINIFS function such as:

D2

=IF(B2="I",MINIFS($C$2:$C$13,$B$2:$B$13,"O",$C$2:$C$13,">"&C2),"FALSE")

Otherwise, if you are indeed using 2013 and don't have access to MINIFS, you can use this array formula... but note that it must be confirmed with CTRL+SHIFT+ENTER instead of just ENTER (doing so correctly will result in Excel putting { }s around your formula in the formula bar):

D2

=IF(B2="I",MIN(IF($B$2:$B$13="O",IF($C$2:$C$13>C2,$C$2:$C$13))),FALSE)