need tricky formula

[Scott R]

I'm looking for a non-array formula that will find the position in a list of the number closest to another number.

In A1:A6, let's say I have 1;2;7; ;8;9

The closest number to 6 would be 7, so the formula would return 3 for the 3rd position.
The closest number to 3 would be 2, so the formula would return 2 for the 2nd position.

I don't think MATCH works for me here.

Thanks!

 

[Scott Huish]

What do you have against array formulas?

 

[Scott R]

I'm going against a lot of data--speed. But I can do that.

 

[Finalfight40]

Does this array formula work for you?

=SMALL(IF((MIN(ABS(A1:A6-C1))=ABS(A1:A6-C1)), ROW(A1:A6), ""), 1)

where C1 is the value you are trying to get closest to.

This doesn't take into account where you are exactly between 2 numbers. For example if you are looking for the closest number to 1.5, you have 1 and 2 the same distance apart.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E
1	1	6	7	7
2	2	3	2	2
3	7
4	8
5	9
6
6b

Cell Formulas
Range	Formula
C1	{=INDEX($A$1:$A$5,MATCH(MIN(ABS(B1-$A$1:$A$5)),ABS(B1-$A$1:$A$5),0))}
D1	{=INDEX($A$1:$A$5,MATCH(MIN(v(ABS(B1-$A$1:$A$5))),v(),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

The v is from Stephen Dunn about 2002; I do not know if it is faster.

 

[Scott R]

Thanks for the help. I ending up using the INDEX version.