How to determine the closest value?

[Eric]

Does anyone have any suggestions on how to determine the closeest number?

Example One
There is a list of number under column A
191,189,183,177,175,171,167,165,159,153,151
and there is a given number in cell B1, 154.
I would like to determine the closest number, which match with the number in
cell B1. On above example, it should return 153 in cell C1.

Example Two
There is a list of number under column A
191,189,183,177,175,171,167,165,155,153,151
and there is a given number in cell B1, 154.
I would like to determine the closest number, which match with the number in
cell B1. On above example, if the given number matches two numbers, which the
difference is the same, then it should return 153 in cell C1.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

 

[Sheeloo]

Sort Column A in Ascending order then enter this in C1
==VLOOKUP(B1,A:A,1,TRUE)

 

[Eric]

Thank you very much for suggestions
Do you have any suggestions on how to use Index function to determine the
value instead of VLookup?
Thank everyone for any suggestions
Eric

 

[Sheeloo]

After sorting
=MATCH(B1,A:A,1)
will give you the position of 153

=INDEX(A:A,MATCH(B1,A:A,1)) will give you 153...

 

[Eric]

Thank you very much for your suggestions
I cannot use the sorting function for my case, do you have any suggestions
to use Index function alone to determine the value?
Thank you very much for any suggestions
Eric

 

[Sheeloo]

I don't think you can get the 'closest' value without sorting your data...

 

[RagDyer]

This *array* formula will return the closest match ... as an answer to your
*first* question:

=INDEX(A1:A11,MATCH(MIN(ABS(A1:A11-B1)),ABS(A1:A11-B1),0))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

If ties are present (2 different numbers are equi-distant from the lookup
value),
the *first* value in the array will be returned.

This *array* formula will return the *largest* value in the array if ties
are present.

=MAX(IF(ABS(A1:A11-B1)=MIN(ABS(A1:A11-B1)),A1:A11))

Since your scenario depicts 153 as the *2nd* value in a tie, and it also is
*not* the largest of the tied values,
I really don't have a suggestion at present for your second question.

 

[Sheeloo]

Thanks RD,

I thought this could not be done without sorting...