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

G

Guest

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

Biff

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
 
J

JMay

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,,
 
B

Biff

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
 
G

Guest

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how do i look up the closest number valuein a lookup? 3
Finding a close match 3
Sort Absolute Values 2
Closest Match Formula 3
Finding Closest Match 1
How to determine the closest value? 7
How to determine the closest value? 2
Find closest match and copy 3

Top