INDEX
How to Find the Closest Value?
This article explains how to identify the closest value to a specific number in Excel using a formula based on absolute differences. We’ll walk through an example to easily locate the nearest value.
Example: Find the Value Closest to 17
Here’s an example where we find the number in the range A2:A5 that is closest to the value 17 entered in cell B2:
| A | B | C | |
|---|---|---|---|
| 1 | Data | Target Value | Closest Value |
| 2 | 10 | 17 | =INDEX(A2:A5, MATCH(MIN(ABS(A2:A5-B2)), ABS(A2:A5-B2), 0)) |
| 3 | 14 | ||
| 4 | 18 | ||
| 5 | 22 |
Formula Explanation
- ABS(A2:A5-B2): Calculates the absolute difference between each value in A2:A5 and the target value in B2.
- MIN(ABS(A2:A5-B2)): Finds the smallest absolute difference, which corresponds to the closest value.
- MATCH(MIN(…), …): Identifies the position of the smallest difference in the range.
- INDEX(A2:A5, …): Returns the value from A2:A5 at the identified position.
Result
After applying the formula, the closest value to 17 (cell B2) is displayed in cell C2:
| A | B | C | |
|---|---|---|---|
| 2 | 10 | 17 | 18 |
| 3 | 14 | ||
| 4 | 18 | ||
| 5 | 33 |
Notes
- If multiple values have the same difference from the target, the formula returns the first match.
Conclusion
By leveraging formulas in Excel, you can easily identify the data point closest to a specified value. This technique is particularly useful for data analysis and conditional comparisons.