Formula for closest number

[GerryZ]

Hello everybody
I need a formula in D2 that find th closest number (criteria number in D1)
esemple if in D1 is 9 should give me back 11 but if in D2 i digit 5 it should give me 4
thank yu for your help

Book1
	A	B	C	D
1	1		CRITERIA	6
2	4		RESULT	5
3	5
4	9
5	11
6	15
7	18
8	19
Foglio1

 

[GerryZ]

This finds nearest +/- but if an actual match it will return that!
Array - CSE

=INDEX(A1:A16,MATCH(MIN(ABS(A1:A16-D1)),ABS(A1:A16-D1),0))

Hello Gaz Nice formula but still not right
because I dont need the same number of the criteria

 

[gaz_chops]

OK this will find nearest +/-, if it find an actual match, it will return the nearest BELOW, don't know if that is acceptable?
Array formula
=IF(MATCH(D1,A1:A16,0),VLOOKUP(D1-1,A1:A16,1),INDEX(A1:A16,MATCH(MIN(ABS(A1:A16-D1)),ABS(A1:A16-D1),0)))

 

[gaz_chops]

Oops made a mistake, try
Array
=IFERROR(IF(MATCH(D1,A1:A16,0),VLOOKUP(D1-1,A1:A16,1)),INDEX(A1:A16,MATCH(MIN(ABS(A1:A16-D1)),ABS(A1:A16-D1),0)))

 

[NARAYANK991]

Hi ,

Try the following array formula , to be entered using CTRL SHIFT ENTER :

=INDEX(List,MATCH(MIN(ABS(IF(List<>CRITERION,List) - CRITERION)),ABS(IF(List<>CRITERION,List) - CRITERION),0))

Narayan

 

[GerryZ]

Oops made a mistake, try
Array
=IFERROR(IF(MATCH(D1,A1:A16,0),VLOOKUP(D1-1,A1:A16,1)),INDEX(A1:A16,MATCH(MIN(ABS(A1:A16-D1)),ABS(A1:A16-D1),0)))

thanks Gaz
till now this is the best solution even if is not 100% correct because in case of same number it returs the belowe number but in some cases the right number is the above number

 

[GerryZ]

Hi ,

Try the following array formula , to be entered using CTRL SHIFT ENTER :

=INDEX(List,MATCH(MIN(ABS(IF(List<>CRITERION,List) - CRITERION)),ABS(IF(List<>CRITERION,List) - CRITERION),0))

Narayan

woooooowwww!!!NARAYANK991 this worrks fine!!!!!
thank you so Much iwill use yur formula
Also a big thank you to Gaz which is very helpful

 

[gaz_chops]

Nice, that looks like it works.

Hi ,

Try the following array formula , to be entered using CTRL SHIFT ENTER :

=INDEX(List,MATCH(MIN(ABS(IF(List<>CRITERION,List) - CRITERION)),ABS(IF(List<>CRITERION,List) - CRITERION),0))

Narayan

 

[gaz_chops]

You're welcome Gerry, close but not quite there.

 

[Aladin Akyurek]

@INGOLF
in this case both 4 and 6

The question you initially posed requires, control+shift+enter...

=INDEX(A1:A8,MATCH(MIN(ABS(A1:A8-D1)),ABS(A1:A8-D1),0))

The remark implies a different question with a two component answer...

a) Control+shift+enter, not just enter:

=INDEX(A1:A5,MATCH(MIN(ABS(IF(A1:A5 < D1,A1:A5-D1,9.99999999999999E+307))),
    ABS(A1:A5-D1),0))

b) Control+shift+enter, not just enter:

=INDEX(A1:A7,MATCH(MIN(ABS(IF(A1:A7 > D1,A1:A7-D1,9.99999999999999E+307))),
    ABS(IF(A1:A7>D1,A1:A7-D1)),0))

 

[GerryZ]

@Aladin great
in case I need both number?
example criteria D1 I've 4 and in the list I've 1-3-4-5-9-21
i want both 3 and 5 because they both have the same distance from 4
hope is clear