+ Reply to Thread
Results 1 to 8 of 8

Formula to find closest match number in a range

  1. #1
    Forum Contributor
    Join Date
    10-06-2015
    Location
    Leeds, England
    MS-Off Ver
    365 Version 2202 Build 16.0.14931.21078
    Posts
    127

    Formula to find closest match number in a range

    I have some code that runs off data in increments. If the code encounters an error it will enter #N/A as the value in the range. This is so I can still plot a graph of the results ignoring the errors.

    I then want to pull out the closest match to a stated number from the range.

    For example if my range is #N/A , 3 , 10 , 15 , 18 and I want to pull out the closest match to 9 then it would give me 10. Using the =INDEX(A1:A5,MATCH(B1,A1:A5,-1),1) will give me 10 but if the number I wanted was 4 I'd want it to give me 3. And if the number was 2 I'd want it to give me 3, or if it was over 18 I'd want it to give me 18 etc.

    I've tried combining the index match function with looking for greater than or less than values but in some instances it won't work. I've attached an example where I have a target of 35.9, and instead of returning 38.27 as the closest match, it is returning the largest number in the range.

    Hope I've explained my requirements well enough.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Formula to find closest match number in a range

    Sorry, but you are going to have to explain your number selection logic in more detail. Why would you want 4 to give you 3, and why would you want 2 to give you 3, etc.?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Contributor
    Join Date
    10-06-2015
    Location
    Leeds, England
    MS-Off Ver
    365 Version 2202 Build 16.0.14931.21078
    Posts
    127

    Re: Formula to find closest match number in a range

    No problem, ideally I'd like to pull out the exact value from the range, in my attached example in this case it would be 35.9, but the values are so precise that I can't pull out an exact match. So I'd like to return the closest match, and if my target value is below the minimum or above the maximum values then I'd like it to return the min or max value respectively. Otherwise, return the closest match. Whilst ignoring any #N/A's.

    Hope this helps.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Formula to find closest match number in a range

    Please try
    =INDEX($E$4:$E$22,MATCH(AGGREGATE(15,6,ABS(E4:E22-C2),1),INDEX(ABS(E4:E22-C2),),))

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Formula to find closest match number in a range

    Almost! Which way do we go if there is an exact midpoint between two values? Up or down?

  6. #6
    Forum Contributor
    Join Date
    10-06-2015
    Location
    Leeds, England
    MS-Off Ver
    365 Version 2202 Build 16.0.14931.21078
    Posts
    127

    Re: Formula to find closest match number in a range

    That's perfect! Does exactly what I wanted! Thanks very much Bo_Ry

  7. #7
    Forum Contributor
    Join Date
    10-06-2015
    Location
    Leeds, England
    MS-Off Ver
    365 Version 2202 Build 16.0.14931.21078
    Posts
    127

    Re: Formula to find closest match number in a range

    It doesn't really matter in my case if it goes above or below the midpoint Ali, what Bo_Ry has provided does exactly what I wanted

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Formula to find closest match number in a range

    Great. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to find the closest number in range
    By mariab1 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-11-2015, 10:52 AM
  2. match closest date from within range matching ID number
    By Vend1301 in forum Excel General
    Replies: 4
    Last Post: 11-18-2015, 02:53 PM
  3. [SOLVED] Find a number closest to 30 in a range with one more criteria (criteria includes text)
    By Sarangsood in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-22-2013, 01:16 PM
  4. [SOLVED] match closest date from within range matching ID number
    By adrianjaeggi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-09-2012, 03:33 PM
  5. find closest date (number) in a range
    By wamp in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-23-2010, 01:50 PM
  6. Find the closest match to a reference number in a row of unsorted
    By Nick Krill in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-01-2006, 04:35 PM
  7. Replies: 4
    Last Post: 12-21-2005, 08:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1