Finding closest value if date is equal to a certain date

Question

I want a formula that finds the moneyness closest to 100 for a certain date. I made this formula:

=(IF("02-01-2009"=C2:C131104;INDEX($K$2:$K$131104;MATCH(MIN(ABS(K2:K131104-100));ABS(K2:K131104-100);0));""))

But it searches the entire sheet instead of only the rows where the date is 02-01-2009.

---

Data

Answer 1

Yours won't work because you need to perform the IF condition before you search for your value. If you are looking to do this without any additional columns (as your attempt does), a more correct formula would be:

=INDEX($K$2:$K$25;MATCH(MIN(IF($C$2:$C$25=$T$9;ABS($K$2:$K$25-100);1000));IF($C$2:$C$25=$T$9;ABS($K$2:$K$25-100);1001);0))

Make sure to enter this with CTRL + SHIFT + ENTER since it's an array formula.

Here cell T9 holds the date I'm matching against. 1000 is somewhat arbitrary (larger than your difference from 100 will ever be). I check the date. If it matches I return moneyness - 100 and if it doesn't I return our arbitrary 1000 so these are excluded from the minimum. Once the minimum finds the smallest difference that matches our date, I perform the match and subsequent index to grab the first moneyness value with the minimum difference.