Find the closest match to a cell , but with a condition.

**Sarangsood** · 12-31-2011, 02:47 AM

Please see in the workbook , it would be easy to understand.
thanks

**XLent** · 12-31-2011, 03:02 AM

=SUM(IF(($B$3:$B$23>0)*ABS($A$3:$A$23-$F$1)=MIN(ABS($A$3:$A$23*($B$3:$B$23>0)-$F$1)),$A$3:$A$23))
confirmed with CTRL + SHIFT + ENTER

same can be applied for M:N by modifying ranges accordingly.
(above would return 4800 & 4500 respectively)

**Sarangsood** · 12-31-2011, 03:09 AM

thanks a lot sir. you are a genius.

**Sarangsood** · 12-31-2011, 03:19 AM

just one more thing @XLENT , i would also need the second closest positive number from both the ranges.

**XLent** · 12-31-2011, 03:25 AM

Revert to SMALL from MIN such that you can adjust "k"

=SUM(IF(($B$3:$B$23>0)*ABS($A$3:$A$23-$F$1)=SMALL(ABS($A$3:$A$23*($B$3:$B$23>0)-$F$1),2),$A$3:$A$23))
confirmed with CTRL + SHIFT + ENTER

would return 5200 & 4300 for A:B, M:N respectively

**JieJenn** · 12-31-2011, 03:44 AM

Just out of curiosity. Why does the result = 10000 when the input = 5000? I am not too familiar with the logic behind array formula, trying my best to understand it.

**XLent** · 12-31-2011, 03:46 AM

Good spot. In that instance there will be 2 closest matches 4800 + 5200

If OP wants just 4800 I would replace the outer SUM with MIN for first match or MAX for last match

**JieJenn** · 12-31-2011, 04:42 AM

Thanks for the explanation. Really impressed with the solution you provided.

**XLent** · 12-31-2011, 05:00 AM

In hindsight for sake of exact match (ABS result is 0) we must insert a pre-emptive test

=MIN(IF($B$3:$B$23>0,IF(ABS($A$3:$A$23-$F$1)=SMALL(ABS($A$3:$A$23*($B$3:$B$23>0)-$F$1),1),$A$3:$A$23)))
confirmed with CTRL + SHIFT + ENTER

change k to 2 for 2nd smallest.