Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: How to find the closest value in a range to a given lookup value?

  1. #1
    New Member
    Join Date
    Apr 2012
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to find the closest value in a range to a given lookup value?

    Is there a function or combination of functions that finds the exact or closest value in a range of values when the range of values is compared to a given lookup value given the following constraints.

    1. The range of values is unsorted.
    2. The range of values may contain zero values.

    Thanks

  2. #2
    Board Regular J.Ty.'s Avatar
    Join Date
    Feb 2012
    Location
    University of Warsaw, Poland
    Posts
    709
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to find the closest value in a range to a given lookup value?

    Hi,

    A1:A19 is the area where you are searching,
    B1 is the value to be (approximately) found
    C1 is helper (displays the minimal distance between what you are searrching for and what is present in the data)
    D1 is the closest value that has been found

    Please read my signature at the bottom.

    Best,

    J.Ty.
    Sheet1

     ABCD
    11615116
    219   
    326   
    48   
    526   
    617   
    726   

    Spreadsheet Formulas
    CellFormula
    A1=RANDBETWEEN(1;30)
    C1{=MIN(ABS(A1:A19-B1))}
    D1=IFERROR(VLOOKUP(B1+C1;A1:A19;1;FALSE);B1-C1)
    A2=RANDBETWEEN(1;30)
    A3=RANDBETWEEN(1;30)
    A4=RANDBETWEEN(1;30)
    A5=RANDBETWEEN(1;30)
    A6=RANDBETWEEN(1;30)
    A7=RANDBETWEEN(1;30)
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4
    My Excel uses ";" to separate arguments in functions, and they sometimes stay there when I copy-paste or insert a screenshot. In such cases please replace ";" by "," everywhere.

  3. #3
    Board Regular BiocideJ's Avatar
    Join Date
    Jan 2012
    Location
    Florida, USA
    Posts
    1,674
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to find the closest value in a range to a given lookup value?

    Single cell solution, although it is a little long.
    With the Range being search in A1:A13
    With the Value being looked up in B1


    If you have Excel 2003 or earlier you will need to use
    =INDEX(A1:A13,IF(ISERROR(MATCH(B1-MIN(ABS(B1-A1:A13)),A1:A13,0)),MATCH(B1+MIN(ABS(B1-A1:A13)),A1:A13,0),MATCH(B1-MIN(ABS(B1-A1:A13)),A1:A13,0)))
    Confirm with CTRL+SHIFT+ENTER

    If you have Excel 2007 or later you can use this slightly shorter version
    =INDEX(A1:A13,IFERROR(MATCH(B1-MIN(ABS(B1-A1:A13)),A1:A13,0),MATCH(B1+MIN(ABS(B1-A1:A13)),A1:A13,0)))
    Also confirmed with CTRL+SHIFT+ENTER
    Last edited by BiocideJ; Apr 18th, 2012 at 10:35 AM. Reason: Forgot to put the INDEX in
    I use Excel 2010 at work.
    Notusingindentsincodeislikenotusingspacesinsentences.Youcanmakeitout,butonlywithdifficulty.

  4. #4
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,125
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to find the closest value in a range to a given lookup value?

    Hi

    Another option

    Using the list values in A1:A7 and the lookup value in B1, try:

    =INDEX(A1:A7,MATCH(MIN(ABS(A1:A7-B1)),ABS(A1:A7-B1),0))
    This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  5. #5
    New Member
    Join Date
    Apr 2012
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to find the closest value in a range to a given lookup value?

    Interesting. Looks like it might work. What would I need to modify if the range is horizontal instead of vertical?

  6. #6
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,125
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to find the closest value in a range to a given lookup value?

    Quote Originally Posted by katmarsar View Post
    Interesting. Looks like it might work. What would I need to modify if the range is horizontal instead of vertical?
    What did you try and what problems are you experiencing?
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  7. #7
    Board Regular BiocideJ's Avatar
    Join Date
    Jan 2012
    Location
    Florida, USA
    Posts
    1,674
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to find the closest value in a range to a given lookup value?

    To change the reference to horizontal instead of vertical, just change A1:Ax to A1:Z1 (or whatever your range is)
    Last edited by BiocideJ; Apr 18th, 2012 at 11:07 AM. Reason: noticed pgc01's formula works fine.
    I use Excel 2010 at work.
    Notusingindentsincodeislikenotusingspacesinsentences.Youcanmakeitout,butonlywithdifficulty.

  8. #8
    New Member
    Join Date
    Apr 2012
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to find the closest value in a range to a given lookup value?

    I must apologize to everyone who has graciously posted a reply to my query. I omitted one important piece. I'm actually trying to locate the relative location of the nearest match within the array, not the actual nearest value. Many thanks.

  9. #9
    Board Regular BiocideJ's Avatar
    Join Date
    Jan 2012
    Location
    Florida, USA
    Posts
    1,674
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to find the closest value in a range to a given lookup value?

    Quote Originally Posted by katmarsar View Post
    I must apologize to everyone who has graciously posted a reply to my query. I omitted one important piece. I'm actually trying to locate the relative location of the nearest match within the array, not the actual nearest value. Many thanks.

    Actually, that is easy since that is the internal piece of the INDEX formula already.
    Using pgc01's formula since it is clearly the best.

    with the lookup range in A1:Z1
    and the lookup value in A2

    =MATCH(MIN(ABS(A1:Z1-A2)),ABS(A1:Z1-A2),0))
    Confirm with CTRL+SHIFT+ENTER
    Last edited by BiocideJ; Apr 18th, 2012 at 11:11 AM. Reason: made the references horizontal since you indicated you need it that way
    I use Excel 2010 at work.
    Notusingindentsincodeislikenotusingspacesinsentences.Youcanmakeitout,butonlywithdifficulty.

  10. #10
    New Member
    Join Date
    Apr 2012
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to find the closest value in a range to a given lookup value?

    That's it. Thanks.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com