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

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

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 wrote in message
...
How can I find the closest match larger( or smaller) than a reference
value
in a row of unsorted data

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

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" wrote in message
...
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 wrote in message
...
How can I find the closest match larger( or smaller) than a reference value
in a row of unsorted data

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

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" 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" wrote in message
...
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 wrote in message
...
How can I find the closest match larger( or smaller) than a reference
value
in a row of unsorted data

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

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" 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" wrote in message
...
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 wrote in message
...
How can I find the closest match larger( or smaller) than a reference
value
in a row of unsorted data