software

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

quot;Nick Krillquot; lt;Nick gt; wrote in message
...
gt; How can I find the closest match larger( or smaller) than a reference
gt; value
gt; 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,,

quot;Biffquot; gt; wrote in message
...
gt; Hi!
gt;
gt; Try this:
gt;
gt; Entered as an array using the key combo of CTRL,SHIFT,ENTER:
gt;
gt; A5 = lookup_value
gt;
gt; =INDEX(A1:J1,MATCH(MIN(ABS(A1:J1-A5)),ABS(A1:J1-A5),0))
gt;
gt; Biff
gt;
gt; quot;Nick Krillquot; lt;Nick gt; wrote in message
gt; ...
gt;gt; How can I find the closest match larger( or smaller) than a reference value
gt;gt; in a row of unsorted data
gt;
gt;
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

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

quot;Biffquot; wrote:

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