## How to find closest or nearest value (number or text string) in Excel?

Supposing you have a list of numbers in a column, and now you are required to find out the closest or nearest value to a given value from the list of numbers. How do you deal with it? Actually, you can find closest value or nearest value in Excel with following steps.

**Find the closest or nearest number with array formula****Easily select all closest numbers in deviation range of given value****Find the closest or nearest text string with a handy tool**

#### Find the closest or nearest number with array formula

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))

**Note:** In this array formula of **{=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))}**,

**B3:B22**is the range that you want to find the specific value**E2**is the searching value that you want to be compared with.

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 |

#### Easily select all closest numbers in deviation range of given value with Kutools for Excel

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.

**Kutools for Excel**- Includes more than 300 handy tools for Excel. Full feature free trial 60-day, no credit card required!** Get It Now**

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:

#### Find the closest or nearest text string with a handy tool

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:

**Kutools for Excel**- Includes more than 300 handy tools for Excel. Full feature free trial 60-day, no credit card required!** Get It Now**

**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:

#### Demo: select all closest values in deviation range of given value

**Kutools for Excel**includes more than 300 handy tools for Excel, free to try without limitation in 30 days.

**Download and Free Trial Now**!

### The Best Office Productivity Tools

#### Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

**Reuse**: Quickly insert**complex formulas, charts**and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

#### Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

**Enable tabbed editing and reading in Word, Excel, PowerPoint**, Publisher, Access, Visio and Project.- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!