» Retrieving the Closest Larger / Closest Smaller Values from a List when there is No Exact Match

Problem:

Listed in cells B2:B5 are values to look up in column A.
We want to create formulas to look up each value and, in the case of there being no exact match, return the closest larger and the closest smaller number that is in the list.

Solution:

To find the closest larger number or an exact match in column A, use the SMALL and COUNTIF functions as shown in the following formula:
=SMALL($A$2:$A$7,COUNTIF($A$2:$A$7,"<"&B2)+1)

To find the closest smaller number or an exact match in column A, use the LARGE and COUNTIF functions as shown in the following formula:
=LARGE($A$2:$A$7,COUNTIF($A$2:$A$7,">"&B2)+1)