Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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.

Easily find all closest text strings by a given word in Excel

In general, you can apply array formula to find out closest numbers in Excel. But how about closest text strings? Here, with the Fuzzy Lookup feature of Kutools for Excel, you can easily find out all closest text strings by a given work in Excel. Full Feature Free Trial 30-day!

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

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 includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

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.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Avel · 1 months ago
    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
  • To post as a guest, your comment is unpublished.
    Daniel King · 1 months ago
    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.
  • To post as a guest, your comment is unpublished.
    DAKOT · 5 months ago
    ITS NOT WORKING its #N/A somehow
    • To post as a guest, your comment is unpublished.
      kellytte · 4 months ago
      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.
  • To post as a guest, your comment is unpublished.
    farolito · 1 years ago
    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
    • To post as a guest, your comment is unpublished.
      kellytte · 11 months ago
      Hi farolito,
      How about changing the value you will compare with to =TODAY() in Cell D1?
  • To post as a guest, your comment is unpublished.
    Liam Ward · 2 years ago
    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?
  • To post as a guest, your comment is unpublished.
    RaymB · 4 years ago
    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??
    • To post as a guest, your comment is unpublished.
      Phil1982 · 4 years ago
      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.
  • To post as a guest, your comment is unpublished.
    Katri · 4 years ago
    It does not work for me! Excel says that there is an error :(
  • To post as a guest, your comment is unpublished.
    Seymour · 4 years ago
    My bad
    This will look for all nearest date to today in column d
    =LARGE(D:D,COUNTIF(d:D,">="&TODAY()))
    WWW.excelireland.com
  • To post as a guest, your comment is unpublished.
    Pradnya · 4 years ago
    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.

    :(
  • To post as a guest, your comment is unpublished.
    Debra · 4 years ago
    Does anyone know why this won't work when I use the whole column for a range (D:D)
  • To post as a guest, your comment is unpublished.
    Seymour Platt · 4 years ago
    I went about this a little differently, Find how many numbers are bigger then the one you are looking for with CountIf()
    Then I used =Large this will find the nth number in a list we have the nth we are looking for in the countIF()

    =LARGE('Fixture and results'!A:A,COUNTIF('Fixture and results'!A:A,T">="&D1))

    It also works with dates - for example nearest date to today
    =LARGE('Fixture and results'!A:A,COUNTIF('Fixture and results'!A:A,">="&TODAY()))
    WWW.excelireland.com
  • To post as a guest, your comment is unpublished.
    Gursh · 4 years ago
    Great formula and advice
  • To post as a guest, your comment is unpublished.
    Roman · 4 years ago
    Hi,
    I think I have done everything correct but it wont give me the closest number it gives me one that is 2 away from it? can anyone help?
  • To post as a guest, your comment is unpublished.
    JN · 5 years ago
    Hi,

    Can someone please tell me how to modify this formula to choose the next higher number instead of the closest one?

    Your response will be sincerely appreaciated.

    Thanks,
  • To post as a guest, your comment is unpublished.
    JN · 5 years ago
    Awesome. You rock buddy. Thanks a million.
  • To post as a guest, your comment is unpublished.
    Srikanth · 5 years ago
    Hi,

    Hope there will be some help sometime, as I have not been able to figure out the solutions for the already posted two problems. Next new one is below.

    In ExcelDoc1.xls, I have 10 values (2,5,7,25,12,52,90,54,66,99) from A1 till J1 and I have got 10 rankings on these 10 values.

    I use the above rankings in another excel ExcelDoc2.xls for reference purposes only, but this excel also gets any other number that may be one of the above 10 values or outside this 10 values.

    Let us say I have a new value, how do I find its ranking dynamically in this ExcelDoc2 itself, removing the first value of 2 dynamically from the 10 values (inside ExcelDoc1.xls) ?

    Let us say I get an existing value, viz., 25, now how do I find out what ranking it equals from the already existing 10 ranks ?

    Thanks,
    S Srikanth
  • To post as a guest, your comment is unpublished.
    %seeker · 5 years ago
    Hello,

    how can I apply it with %. Like if I want to get the closet percentage value within a percentage set of data.
    WIth this formula I get an error all the time…


    Thanks
  • To post as a guest, your comment is unpublished.
    Srikanth · 5 years ago
    Hi,

    I have two distinct arrays. These two arrays are present across thousands of rows. Row#1 has these values 1,3,5,7,9 respectively, from A1 till E1, G1 has 11. Please note that there is no value present in F1. I would like to combine these inside excel LARGE and SMALL functions for the 1st array param.

    Note :
    1) Cell G1's value is a result or a computed value.
    2) I'm neither able to use NAMED RANGE feature of MS Excel nor in a position to remove Column F as a shortcut, due to thousands of rows.

    Thanks,
    S Srikanth
  • To post as a guest, your comment is unpublished.
    Trisch · 5 years ago
    Love this! Thanks so much for posting it :lol:
  • To post as a guest, your comment is unpublished.
    Srikanth · 5 years ago
    Hi,

    This does not seem to work if the array contains a mix of +ve and -ve numbers with decimal parts. For instance, how do I find the next lowest or highest number for -6392.52. You can copy paste the below 17 values in a row. Please help me know in case you find it out.

    5182.47 4432.65 5285.95 3259.14 1731.73 1011.25 66.45 -203.18 -926.70 -1857.41 -3488.99 -4006.90 -4804.79 -5339.44 -6046.62 -6414.55 -6392.52

    Thanks,
    S Srikanth
  • To post as a guest, your comment is unpublished.
    Ashley · 5 years ago
    Can you use this formula when you want to select the value from a data set on another sheet? I tried it, and it doesn't seem to work.
  • To post as a guest, your comment is unpublished.
    Clarification · 5 years ago
    For clarification: you need to be in the formula when you hit Ctrl+Shift+Enter. Simply selecting the cell and punching the buttons will NOT work. Hopefully this clears up any confusion
  • To post as a guest, your comment is unpublished.
    OriginalMAF · 5 years ago
    Good afternoon,
    How can I select the next larger number? Setting up a shipping calculation sheet and it does me no good to select the smaller (if not exact) number. My formula is based on Vt³(xyz) * Qnty of parts. My data set is a rather large selection of box dimensions.

    =VLOOKUP(AB9,Sheet6!A2:L87,1,TRUE)
    &
    =VLOOKUP(V13,BoxSize,2,-TRUE)
  • To post as a guest, your comment is unpublished.
    ast · 5 years ago
    Good one , and working
  • To post as a guest, your comment is unpublished.
    Jeff G · 5 years ago
    This formula works great, but one callout. Make sure you do not have any text in the search range. It will return an error. I had tried to search a list of zip codes, and the list included Canadian zips (Alphanumeric). It would not work until I removed them. Otherwise it works great! Thanks!
  • To post as a guest, your comment is unpublished.
    Austin J · 5 years ago
    Can i use a array with this formula?
  • To post as a guest, your comment is unpublished.
    Tom D · 5 years ago
    Tis is great but . . . How can I use this to return another value for example, I have two columns and I want to find the match in one column but return the vaule in the adjacent column.
  • To post as a guest, your comment is unpublished.
    Ali · 5 years ago
    This is an Array Formula, and to enter this formula you need to hit CTRL + SHIFT + ENTER (true for excel 2010)
  • To post as a guest, your comment is unpublished.
    Tudor · 5 years ago
    Thanks! Fabulous formula!
  • To post as a guest, your comment is unpublished.
    DavidK81 · 5 years ago
    Absolutely AWESOME!!
  • To post as a guest, your comment is unpublished.
    JARED · 5 years ago
    Actually, I meant thanks anonymous rockin' Rockstar... Marcus, you're wrong, it does work and I'm pretty sure you didn't hit ctrl + Alt + Enter
  • To post as a guest, your comment is unpublished.
    JARED · 5 years ago
    TOTALLY AWESOME!! Thanks Marcus! You ARE a rockin' rockstar
  • To post as a guest, your comment is unpublished.
    Not Marcus Kirby · 5 years ago
    Yes it does Marcus, You didn't do something right!!

    Probably didn't hit clt-Alt-enter
  • To post as a guest, your comment is unpublished.
    Marcus Kirby · 5 years ago
    Oh no it doesn't! I have used your precise example and it does not do what you say.
    • To post as a guest, your comment is unpublished.
      Michael hannifan · 4 years ago
      hey Marcus Kirby you might have the formula wright but your ref. to the cell range A1:A20 could have a formula in it. for instance if A2 has this formula =if(c15>10,100,50). so your answer will be either 50 or 100 BUT the numbers aren't numbers anymore excel looks at them as words and if you click on the cell A2 and press F9 you will get the answer "50". if that is so then you need to put the Absolute formula in your cell so it will be a number again. like this =abs(if(c15>10,100,50)) the answer now will be just 50 or just 100 and this will fix your error.