Find the closest numbers to a specific requirement.

Hi,

I have a range of values and require a formula to find the closest numbers on either side of 20 as below:

0.0000000000
2.2727272727
2.2727272727
6.5249990000
6.5250000000
11.9136353636
11.9136363636
17.3022717273
17.3022727273
22.6909080909
22.6909090909
28.0795444545
28.0795454545


so the lower bound closest to the 20 = 17.3022727273
and upper bound closest to 20 = 22.6909080909

thanks for your help.
v

If your data in column A, try this

the lower bound closest
=AGGREGATE(14,6, A1:A100/(A1:A100<=20),1)

upper bound closest
=AGGREGATE(15,6, A1:A100/(A1:A100>=20),1)

Thank you windknife and HansDouwe. Both solutions worked for me.

Apreciate the quick and prompt response.

You are welcome.
-----------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.