• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find a value in a range that is nearest and within +/- of another value

GB

Member
Hi,
I have partially solved a problem by finding the nearest value in a range (A2:A28) of another value (in cell C1) by using the following formula:

{=INDEX($A$2:$A$28,MATCH(MIN(ABS($A$2:$A$28-$C$1)),ABS($A$2:$A$28-$C$1),0))}

The part I am having trouble with to find the nearest value in a range (A2:A28) to the number in cell C1, if it is within +/- 3 of C1.

Can anyone help please? See attached file.
Regards
GB
 

Attachments

p45cal

Well-Known Member
try array-entering:
Code:
=IFERROR(INDEX($A$2:$A$28,MATCH(MIN(IF(ABS($C$1-$A$2:$A$28)<=$C$7,ABS($C$1-$A$2:$A$28))),IF(ABS($C$1-$A$2:$A$28)<=$C$7,ABS($C$1-$A$2:$A$28)),0)),"None")
If there's a tie (one my be, say, 1 under and the other 1 over), the first in the list is chosen.
 
Last edited:

Peter Bartholomew

Well-Known Member
This looks different only because I never use direct cell referencing. I first define a named formula delta to perform the array calculation
= IF( ABS(Numbers-Value)<=3, ABS(Numbers-Value) )
Then that is used as the basic for the INDEX+MATCH
= INDEX( Numbers, MATCH( MIN(delta), delta, 0 ) )

Since I am using an Office 365 dynamic array version of Excel, I could also use
= XLOOKUP( MIN(delta), delta, Numbers )
or
= FILTER( Numbers, delta=MIN(delta), "none" )
 

GB

Member
This looks different only because I never use direct cell referencing. I first define a named formula delta to perform the array calculation
= IF( ABS(Numbers-Value)<=3, ABS(Numbers-Value) )
Then that is used as the basic for the INDEX+MATCH
= INDEX( Numbers, MATCH( MIN(delta), delta, 0 ) )

Since I am using an Office 365 dynamic array version of Excel, I could also use
= XLOOKUP( MIN(delta), delta, Numbers )
or
= FILTER( Numbers, delta=MIN(delta), "none" )
Hi Peter, appreciate your response. I haven't tried xlookup yet but you have given me something to work on. Thank you.
Regards
GB
 

GB

Member
try array-entering:
Code:
=IFERROR(INDEX($A$2:$A$28,MATCH(MIN(IF(ABS($C$1-$A$2:$A$28)<=$C$7,ABS($C$1-$A$2:$A$28))),IF(ABS($C$1-$A$2:$A$28)<=$C$7,ABS($C$1-$A$2:$A$28)),0)),"None")
If there's a tie (one my be, say, 1 under and the other 1 over), the first in the list is chosen.
Hi p45cal, also appreciate you taking the time to respond. Thank you
regards
GB
 
Top