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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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,,
 
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
 
Thanks for the clarification Biff;
Jim

Biff said:
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
Click to expand...
 
Back
Top