Find the closest match to a reference number in a row of unsorted

Data is random in nature and unsorted. Generally between 10 and 100, two
decimal precision (see below). How do I find the position of the entry
closest a given value, say 45.11

45.54 45.56 45.32 45.5 45.42 45.35 45.46 45.4 44.97 45 45.05

With your data in A1:K1:

=MATCH(MIN(A1:K1-45.11),A1:K1-45.11,0)

Array-entered, which means commit with CTRL+SHIFT+ENTER instead of just with
ENTER

In the formula bar, the formula will appear in curly brackets, to prove it
is an array formula

--
Kind regards,

Niek Otten

"Nick Krill" <NickKrill@discussions.microsoft.com> wrote in message
news:F3F0A833-C20B-4228-B208-0F0795B8777E@microsoft.com...
> Data is random in nature and unsorted. Generally between 10 and 100, two
> decimal precision (see below). How do I find the position of the entry
> closest a given value, say 45.11
>
> 45.54 45.56 45.32 45.5 45.42 45.35 45.46 45.4 44.97 45 45.05
>

Neik,

Your formula returns the position of the lowest number for me even if one of
the given numbers is 45.11. Should the range subtractions not be enclosed
in ABS() functions as in:

=MATCH(MIN(ABS(A1:K1-45.11)),ABS(A1:K1-45.11),0)


--
Regards

Sandy
sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk


"Niek Otten" <nicolaus@xs4all.nl> wrote in message
news:%23OHCZrwDGHA.740@TK2MSFTNGP12.phx.gbl...
> With your data in A1:K1:
>
> =MATCH(MIN(A1:K1-45.11),A1:K1-45.11,0)
>
> Array-entered, which means commit with CTRL+SHIFT+ENTER instead of just
> with ENTER
>
> In the formula bar, the formula will appear in curly brackets, to prove it
> is an array formula
>
> --
> Kind regards,
>
> Niek Otten
>
> "Nick Krill" <NickKrill@discussions.microsoft.com> wrote in message
> news:F3F0A833-C20B-4228-B208-0F0795B8777E@microsoft.com...
>> Data is random in nature and unsorted. Generally between 10 and 100, two
>> decimal precision (see below). How do I find the position of the entry
>> closest a given value, say 45.11
>>
>> 45.54 45.56 45.32 45.5 45.42 45.35 45.46 45.4 44.97 45 45.05
>>
>
>

Absolutely right, Sandy!

Thanks for the correction.

--
Kind regards,

Niek Otten

"Sandy Mann" <sandymann2@mailinator.com> wrote in message
news:elb0k2wDGHA.3820@TK2MSFTNGP12.phx.gbl...
> Neik,
>
> Your formula returns the position of the lowest number for me even if one
> of the given numbers is 45.11. Should the range subtractions not be
> enclosed in ABS() functions as in:
>
> =MATCH(MIN(ABS(A1:K1-45.11)),ABS(A1:K1-45.11),0)
>
>
> --
> Regards
>
> Sandy
> sandymann2@mailinator.com
> Replace@mailinator.com with @tiscali.co.uk
>
>
> "Niek Otten" <nicolaus@xs4all.nl> wrote in message
> news:%23OHCZrwDGHA.740@TK2MSFTNGP12.phx.gbl...
>> With your data in A1:K1:
>>
>> =MATCH(MIN(A1:K1-45.11),A1:K1-45.11,0)
>>
>> Array-entered, which means commit with CTRL+SHIFT+ENTER instead of just
>> with ENTER
>>
>> In the formula bar, the formula will appear in curly brackets, to prove
>> it is an array formula
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> "Nick Krill" <NickKrill@discussions.microsoft.com> wrote in message
>> news:F3F0A833-C20B-4228-B208-0F0795B8777E@microsoft.com...
>>> Data is random in nature and unsorted. Generally between 10 and 100, two
>>> decimal precision (see below). How do I find the position of the entry
>>> closest a given value, say 45.11
>>>
>>> 45.54 45.56 45.32 45.5 45.42 45.35 45.46 45.4 44.97 45 45.05
>>>
>>
>>
>
>