How to Find the Closest Value in Google Sheets

To find the closest value in Google Sheets, you can use a combination of the INDEX, MATCH, MIN, and ABS functions. Here's a step-by-step guide:

1. Assume you have a list of values in column A (from A2 to A10) and a target value in cell D1.
2. You want to find the closest value from the list to the target value.

Example

   A      B      C      D
1        Target:      15
2   10
3   14
4   18
5   20
6   25
7   30
8   35
9   40
10  45

In this example, the list of values is in cells A2 to A10, and the target value is 15 (cell D1).

Follow these steps to find the closest value to the target value:

1. In an empty cell (e.g., B2), write the following formula:

   =INDEX(A2:A10,MATCH(MIN(ABS(A2:A10-D1)),ABS(A2:A10-D1),0))

2. Press Enter.

The formula will return the closest value from the list (column A) to the target value (cell D1). In our example, the closest value to 15 is 14.

Here's a brief explanation of the formula:

• ABS(A2:A10-D1) calculates the absolute difference between each value in the list and the target value.
• MIN(ABS(A2:A10-D1)) finds the smallest absolute difference.
• MATCH(MIN(ABS(A2:A10-D1)),ABS(A2:A10-D1),0) finds the position of the smallest absolute difference in the list.
• INDEX(A2:A10,MATCH(...)) returns the value from the list that corresponds to the position of the smallest absolute difference.