find closest match to a reference number in a row of numbers

How can I find the closest match larger( or smaller) than a reference value
in a row of unsorted data

Hi!

Try this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

A5 = lookup_value

=INDEX(A1:J1,MATCH(MIN(ABS(A1:J1-A5)),ABS(A1:J1-A5),0))

Biff

"Nick Krill" <Nick [email protected]> wrote in message
news:[email protected]...
> How can I find the closest match larger( or smaller) than a reference
> value
> in a row of unsorted data

Biff:

This is very nice. Can you explain or interpret how the formula is treating or
bringing back the both the match(row) and the match(column) arguments of the
index function?

Confused here...
TIA,,

"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
> Try this:
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
>
> A5 = lookup_value
>
> =INDEX(A1:J1,MATCH(MIN(ABS(A1:J1-A5)),ABS(A1:J1-A5),0))
>
> Biff
>
> "Nick Krill" <Nick [email protected]> wrote in message
> news:[email protected]...
>> How can I find the closest match larger( or smaller) than a reference value
>> in a row of unsorted data
>
>

Hi!

It's only referencing a single (row) array so there is no column argument.

Simply determine the minimum deviation from the lookup_value. Since the OP
stated that it could be either above or below the lookup_value we have to
use the ABS function so that negative deviations are made equal to positive
deviations.

Biff

"JMay" <[email protected]> wrote in message
news:hnSpf.61035$WH.17922@dukeread01...
> Biff:
>
> This is very nice. Can you explain or interpret how the formula is
> treating or bringing back the both the match(row) and the match(column)
> arguments of the index function?
>
> Confused here...
> TIA,,
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> Hi!
>>
>> Try this:
>>
>> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
>>
>> A5 = lookup_value
>>
>> =INDEX(A1:J1,MATCH(MIN(ABS(A1:J1-A5)),ABS(A1:J1-A5),0))
>>
>> Biff
>>
>> "Nick Krill" <Nick [email protected]> wrote in message
>> news:[email protected]...
>>> How can I find the closest match larger( or smaller) than a reference
>>> value
>>> in a row of unsorted data
>>
>>
>
>

Thanks for the clarification Biff;
Jim

"Biff" wrote:

> Hi!
>
> It's only referencing a single (row) array so there is no column argument.
>
> Simply determine the minimum deviation from the lookup_value. Since the OP
> stated that it could be either above or below the lookup_value we have to
> use the ABS function so that negative deviations are made equal to positive
> deviations.
>
> Biff
>
> "JMay" <[email protected]> wrote in message
> news:hnSpf.61035$WH.17922@dukeread01...
> > Biff:
> >
> > This is very nice. Can you explain or interpret how the formula is
> > treating or bringing back the both the match(row) and the match(column)
> > arguments of the index function?
> >
> > Confused here...
> > TIA,,
> >
> > "Biff" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi!
> >>
> >> Try this:
> >>
> >> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
> >>
> >> A5 = lookup_value
> >>
> >> =INDEX(A1:J1,MATCH(MIN(ABS(A1:J1-A5)),ABS(A1:J1-A5),0))
> >>
> >> Biff
> >>
> >> "Nick Krill" <Nick [email protected]> wrote in message
> >> news:[email protected]...
> >>> How can I find the closest match larger( or smaller) than a reference
> >>> value
> >>> in a row of unsorted data
> >>
> >>
> >
> >
>
>
>