Thread: The Closest numbers to a number from a list ???

Hi experts. I have a data list (A1:A18) as it shows below:

10
15
20
25
30
35
40
45
50
55
60
65
70
75
80
85
90
95

in cell C1 we will set any number among 10 and 95, let's say, 41.

How can I find the two closest numbers to number (set in cell C1) from the list (list in ascendent order from top to bottom)?.

For this case, the closest to 41:

In Cell D1 (number before): 40
In Cell E1 (number after): 45

If I enter 95 the results that I expect will be:

Low Match: 90
High Match: 95

in the inverse case:

If I enter 10 the results that I expect will be:

Low Match: 10
High Match: 15

If my number is 45, are the two closest numbers 40 and 45 the results that I expect will be:

Low Match: 40
High Match: 45

And number in cell C1 not always would be integer, per example If the number is 26.3 the results that I expect will be:

Low Match: 25
High Match: 30

Thanks in advance.

One way:

D1: =SMALL(INDEX(IF(ABS($A$1:$A$18-$C$1)=SMALL(ABS($A$1:$A$18-$C1),{1,2}),$A$1:$A$18),0),COLUMNS($D1:D1))
confirmed with CTRL + SHIFT + ENTER
copied to E1

this will return the two closest matching values in ascending order (irrespective of which is closer to target).

another is to add a helper with inverted range
see attached

Thank you DonkeyOte and martindwilson, both options work perfect!