# 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

• 10.5 KB Views: 8

#### 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:
• herofox

#### 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