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

[GB]

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

 

[p45cal]

try array-entering:

=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.

 

[Peter Bartholomew]

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]

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]

try array-entering:

=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