Skip to main content

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

Author: Xiaoyang Last Modified: 2020-04-28

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

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.
note ribbon 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: Over 300 handy tools at your fingertips! Start your 30-day free trial with no feature limitations today. Download Now!

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


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!
Comments (42)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How do we do this if our data is filtered?
This comment was minimized by the moderator on the site
copy the filtered data to a new sheet
This comment was minimized by the moderator on the site
Using the formula how would you return the value next to 17 if there was another column next to number like names. So if 17 is the closest in rang the name next to 17 (John) would be returned?


Example: 18 is nearest to 17 so the return value would be John


Numbers Names
38 Tammy
17 John
20 Amy
This comment was minimized by the moderator on the site
You can use the Approximate match of VLOOKUP function to solve this problem.
=VLOOKUP(E2,A1:B15,2,TRUE)
This comment was minimized by the moderator on the site
I used this formula =INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0)) and it works great. However i have found that where the source number is exactly between two numbers in the range, the lower range number is selected to be the closest.

eg: Searching for the closest number to 9 in the range: 6, 8, 10, 12. It will chose 8 instead of 10. Rounding convention is to round up if exactly half way between. Is there a workaround? Thanks.
This comment was minimized by the moderator on the site
ITS NOT WORKING its #N/A somehow
This comment was minimized by the moderator on the site
Hi DAKOT,
=INDEX(A1:A20,MATCH(MIN(ABS(A1:A20-D1)),ABS(A1:A20-D1),0)) is an array formula, after entering it, please remember to press the Ctrl + Shift + Enter keys together.
This comment was minimized by the moderator on the site
Hi,
im using that formula in finding closest date, it is working.but i want to add condition: closest date that is less than 30 days of the current date (today).it is possible?
Anyone can help please?thank you
This comment was minimized by the moderator on the site
Hi farolito,
How about changing the value you will compare with to =TODAY() in Cell D1?
This comment was minimized by the moderator on the site
Hi, I can get it to work, amazing, BUT not when I input '1' as my 'match_type', instead of the '0' that you used. I want to return values less than or equal to, not just closest to +/-. If I enter 1 instead of 0, it doesn't work. Thoughts on why this might be?
This comment was minimized by the moderator on the site
Great formula -thank you-just a quick question. Anyone know how to highlight the cell that is closest in the match so in long lists it is easy to find??
This comment was minimized by the moderator on the site
If you have the row number from the formula above then you could set a conditional formatting rule on the search array to highlight a cell if it lies on that row.
This comment was minimized by the moderator on the site
It does not work for me! Excel says that there is an error :(
This comment was minimized by the moderator on the site
My bad This will look for all nearest date to today in column d =LARGE(D:D,COUNTIF(d:D,">="&TODAY())) http://WWW.excelireland.com
This comment was minimized by the moderator on the site
Hi, I tried the above formula. however, it is giving me a #N/A. 1. Copy pasted the range from A2 to A43. 2. Formula given was : =INDEX(A2:A43,MATCH(MIN(ABS(A2:A43-H1)),ABS(A2:A43-H1),0)) 3. Press control +Shift +enter Can anybody help me as to what wrong I am doing. :(
This comment was minimized by the moderator on the site
Replace the " , " separator for " ; "
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations