» Retrieving the Closest Larger and Closest Smaller Values from a List When There Is No Exact Match

Problem:

Listed in 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 colsest 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)


Example:

List1
2
5
4
10
1
6

Value to look______Closest larger value____Closest smaller value
3__________________4_______________________2
5__________________5_______________________5
7__________________10______________________6
9__________________10______________________6
Screenshot // Retrieving the Closest Larger and Closest Smaller Values from a List When There Is No Exact Match