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

 

[Aladin Akyurek]

See post #19 and #29...

 

[jasonb75]

Isn't that formula just a long winded version of

=LARGE(IF(A1:A8<D1,A1:A8,""),1)&" - "&SMALL(IF(A1:A8>D1,A1:A8,""),1)

 

[gaz_chops]

No he want's the nearest match above OR below, but not equal to!

 

[jasonb75]

I didn't notice that the board bit a chunk out of my formula, it should have said

=LARGE(IF(A1:A8<D1,A1:A8),1)&" - "&SMALL(IF(A1:A8>D1,A1:A8),1)

and a similar afterthought

=MAX(IF(A1:A8<D1,A1:A8)&" - "&MIN(IF(A1:A8>D1,A1:A8)

Both of which, as far as I can see, return the same results as the formula in post #30

The only reason I didn't suggest it earlier was because my interpretation of the requirement would have needed something more like

=CHOOSE(SIGN(IFERROR(SMALL(IF(A1:A8<D1,ABS(A1:A8-D1),""),1),1E+100)-IFERROR(SMALL(IF(A1:A8>D1,A1:A8-D1,""),1),1E+100))+2,LARGE(IF(A1:A8<D1,A1:A8,""),1),LARGE(IF(A1:A8<D1,A1:A8,""),1)&" - "&SMALL(IF(A1:A8>D1,A1:A8,""),1),SMALL(IF(A1:A8>D1,A1:A8,""),1))

which works, but is a bit long, I was trying to figure out a more succinct version before posting.

 

[GerryZ]

@Jasonb75
excellent formula
only missing a couple of pharentesis but works fine
thank you!

Book1
	A	B	C
1	31		21
2	8		18 - 22
3	25
4	24
5	9
6	5
7	18
8	22
Foglio1

Cell Formulas
Range	Formula
C2	{=MAX(IF(A1:A8))&" - "&MIN(IF(A1:A8>C1,A1:A8))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jasonb75]

@Jasonb75
excellent formula
only missing a couple of pharentesis but works fine
thank you!

You're welcome Gerry.

Apologies for the missing brackets, it seems to be one of my regular bad habits when I type the formula to the forum instead of copying it from excel.

When I was reading through the earlier posts in this thread I was under the impression that you only wanted the 2 numbers returned in the event of a tiebreak, but otherwise just return the closest number.

If that is the case, the long formula at the end of my earlier post does that, but it could benefit from some refinement first if you are going to use it.