CODEWITHSUNDEEP
excelexcel-formulaexcel-match
anwi12ad
anwi12ad

Reputation: 49

Finding closest value if date is equal to a certain date

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

enter image description here

Upvotes: 2

Views: 159

Answers (1)

Mark Balhoff
Mark Balhoff

Reputation: 2356

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.

Upvotes: 1

Related Questions