How to find closest or nearest value in Excel?

Suppose you have a list of numbers in Column B and need to determine which value is closest to a given number—for example,18. Using an array formula in Excel enables you to efficiently identify this without scanning through the list manually.

To get started, select a blank cell, and enter the following formula. Once you have typed the formula, be sure to press Ctrl + Shift + Enter instead of just Enter. This will ensure the formula runs as an array formula, which is necessary for it to function correctly:

=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))

This approach is most suitable when you need to retrieve the single closest number from a continuous range. It works well in most cases where numerical accuracy and exact matches are crucial. However, keep in mind that array formulas can be resource-intensive in very large datasets. If you experience performance issues or receive error messages like #VALUE!, double-check your cell references and ensure you press Ctrl + Shift + Enter correctly.

There are times when you may not need just the single closest value but instead want to select all numbers that fall within a certain range of your target value—often called a deviation range. Kutools for Excel offers a practical solution through its Select Special Cells feature, allowing quick selection of all values within a specified difference from your target.

For example, let’s say your target value is 18 and you’ve determined a deviation value of 2. This means you want to select all values in your range that are between 16 (18–2) and 20 (18+2). Here’s how you can achieve this step by step:

1. Select the range you wish to search (for example, B3:B22), then go to Kutools > Select > Select Specific Cells.

2. In the Select Specific Cells dialog:

• Under Selection type, choose Cell.
• In Specific type:
  - Set the first drop-down list to Greater than or equal to and enter 16 in the box.
  - Set the second drop-down to Less than or equal to and enter 20.

3. Click OK to execute. Kutools will notify you how many cells met your criteria and highlight all the closest values within the specified deviation as shown below:

This solution is ideal for quickly identifying all nearby values in bulk, especially when handling broad ranges with variable tolerances. Note that the accuracy of your selection depends on clearly setting your deviation—if your deviation is too narrow or wide, you may miss relevant data or include unwanted values.

For users seeking automation or who need to perform customized closest value searching—either for numerical or text data—across several sheets or large datasets, a VBA macro can be an efficient and flexible solution. By programming Excel to systematically check the difference between your target and all candidates, you can retrieve not only the closest number, but also the nearest string by text distance.

This approach is advantageous when integrated automation is required, especially over ranges too large for manual methods or when applying recurring tasks. However, keep in mind that VBA macros require enabling macros and a basic familiarity with the VBA environment. Before running any macro, always back up your data to prevent unintended loss.

1. Click Developer  > Visual Basic. In the Microsoft Visual Basic for Applications window, click Insert > Module, and copy the following code into the module:

Function FindClosest(rng As Range, target As Double) As Double
    Dim cell As Range
    Dim minDiff As Double
    Dim closestValue As Double
    
    minDiff = 1E+99
    For Each cell In rng
        If Abs(cell.Value - target) < minDiff Then
            minDiff = Abs(cell.Value - target)
            closestValue = cell.Value
        End If
    Next cell  
    FindClosest = closestValue
End Function 

2. Then, go to your worksheet, and enter this formula: =FindClosest(B3:B22, E2) into a blank cell. Press Enter key to get the closest value.

When reviewing or presenting data, it's often helpful to visually identify values closest to a target without filtering or rearranging your data. Excel’s built-in Conditional Formatting feature enables you to highlight the cells that are nearest to your target value, making them easy to spot at a glance. Though this method does not return the exact value itself, it is effective for quick data analysis and visual emphasis.

The primary advantage of this method is non-destructive, dynamic highlighting that can adapt as data or target values change. It’s especially well-suited for dashboards, presentations, and review scenarios where visibility is key. It may be less precise if more than one value shares the same “closeness,” and it does not output the value itself for further processing.

1. Select the range of cells you want to analyze (for example, B3:B22).

2. On the Home tab, click Conditional Formatting > New Rule.

3. Choose Use a formula to determine which cells to format in the dialog box. Then, in the formula box, enter the following formula:

=ABS(B3-$E$2)=MIN(ABS($B$3:$B$22-$E$2))

4. Click Format and choose a highlight color, then click OK, and OK again to apply the rule.

This will highlight all cells in your selected range whose values are equally closest to the target value in E2.

If you are working with large ranges or experience unexpected results, double-check that your references are correct and that absolute/relative references are set as intended (use $ to lock the target cell and the range references).