For example, you have a list of numbers in Column A, and now you will find the closest value or the nearest value of 18 from the Column A. You can do it as follows:

Select a blank cell, and enter below formula, and press the Ctrl + Shift + Enter keys together.

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

Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future! Read more…     Free trial

Sometimes, you may want to find out and select all closet values to the given value in a range. Actually, we can define a deviation value, and then apply Kutools for Excel’s Select Special Cells utility to find out and select all closest values within the diviation range of give value easily.

For example, in our example we define the deviation value as 2, and the given value is 18. Therefore, we need to find out and select values between 16 (=18-2) and 20 (=18+2). Please view following steps:

1. Select the range where you will search for closest values to the give value, and then click Kutools > Select > Select Specific Cells.

2. In the opening Select Specific Cells dialog box,
(1) Check the Cell option in the Selection type section;
(2) In the Specific type section, click the first drop down list and select Greater than or equal to from it and type 16 into following box, and then select Less than or equal to from the second drop down list and type 20 into following box. See left screenshot:

3. Click the Ok button to apply this utility. Then a dialog box comes out and shows you how many cells have been selected. And you will see all closest values within the deviation range of the given value are selected as below screenshot shown:

If you have Kutools for Excel installed, you can apply its Fuzzy Find feature to find out closest text strings from a given range easily in Excel. Please do as follows:

1. Click Kutools > Find > Fuzzy Lookup to enable the Fuzzy Lookup pane in your workbook.

2. In the Fuzzy Lookup pane, please configure as follows；
(1) Check the Specified option, and select the range where you will look for closest text strings;
(2) Check the Find by specified text option;
(3) Go to the Text box, and type the specified text whose closest text strings you will find;
(4) In the Maximum number of different characters box, please type a number. In my case, I type 1;
(5) In The cell string length is at least box, please type a number. In my case, I type 5;

3. Click the Find button.

Now expand the search results, and you will see all closest text strings are listing in bulk. See screenshot: