first smallest and second smallest value dependent on a condition

[virencm]

Hello,

I have a range of values as below:

3489
3489
2345
5600
3200
7800
2345
12000
3200
3200
3489

I need a formula to find the 1st smallest value (I use small(range,1) to get this) and the 2nd smallest value that is closest to the difference of 3000

for the example above, the smallest value would be 2345 and the second smallest value that meets the condition would be 5600

let me know if you need further information and thanks for your help in advance.

Viren

[SimonLock]

Assuming your values are in A1:A11, enter this by pressing CTRL, SHIFT and ENTER to get an array formula.

=IF(ABS(C1+3000-MIN(IF(A1:A11>=C1+3000,A1:A11)))<ABS(C1+3000-MAX(IF(A1:A11<=C1+3000,A1:A11))),MIN(IF(A1:A11>=C1+3000,A1:A11)),MAX(IF(A1:A11<=C1+3000,A1:A11)))