Closest value lookup?

[ADK]

I have a sheet with specific numbers. On another sheet, the user would input
a value. I like excel to find that number or the closest number then return
the value in row/column X.

Example:

User inputs 124.5
The closest value in lookup is 124.25
then formula in the next cell returns the value in row/column X

Any way of doing this?

 

[T. Valko]

Is your list of numbers sorted? In what order, ascending or descending? You
want the closest whether it's over or under?

Biff

 

[ADK]

over, under...doesn't matter....which ever is closer

Example of chart below:

6 6 1/4 6 1/2 6 5/8 6 11/16 6 3/4 6 13/16
1 6 6.25 6.5 6.625 6.6875 6.75 6.8125
2 100 100.5 101 101.25 101.375 101.5 101.625
3 106 106.75 107.5 107.875 108.0625 108.25 108.4375
4 200 201 202 202.5 202.75 203 203.25
5 206 207.25 208.5 209.125 209.4375 209.75 210.0625
6 300 301.5 303 303.75 304.125 304.5 304.875
7 306 307.75 309.5 310.375 310.8125 311.25 311.6875
8 400 402 404 405 405.5 406 406.5
9 406 408.25 410.5 411.625 500.1875 500.75 501.3125
10 500 502.5 505 506.25 506.875 507.5 508.125
11 506 508.75 511.5 600.875 601.5625 602.25 602.9375
12 600 603 606 607.5 608.25 609 609.75
13 606 609.25 700.5 702.125 702.9375 703.75 704.5625
14 700 703.5 707 708.75 709.625 710.5 711.375
15 706 709.75 801.5 803.375 804.3125 805.25 806.1875
16 800 804 808 810 811 900 901
17 806 810.25 902.5 904.625 905.6875 906.75 907.8125
18 900 904.5 909 911.25 1000.375 1001.5 1002.625
19 906 910.75 1003.5 1005.875 1007.0625 1008.25 1009.4375
20 1000 1005 1010 1100.5 1101.75 1103 1104.25
21 1006 1011.25 1104.5 1107.125 1108.4375 1109.75 1111.0625
22 1100 1105.5 1111 1201.75 1203.125 1204.5 1205.875
23 1106 1111.75 1205.5 1208.375 1209.8125 1211.25 1300.6875

 

[T. Valko]

Ok, so do you want to find the closest number in column B and return one of
the numbers on the same row from one of the other columns?

Give me an example based on the screencap you posted.

Biff

 

[ADK]

Row 1 and Column A are resultants in the chart based on user input value

Example 1:
a user inputs a number in A1 (input sheet) such as: 208.75
the closest value would be 208.5 (cell D6) (data Sheet)
Cell A2 (input sheet) would have the formula and return the value 5 (cell
A6)
Cell A3 (input sheet) would have the formula and return the value 6 1/2
(cell D1)

Example 2:
a user inputs a number in A1 (input sheet) such as: 810
the closest value would be 810.25 (cell C18) (data Sheet)
Cell A2 (input sheet) would have the formula and return the value 17 (cell
A18)
Cell A3 (input sheet) would have the formula and return the value 6 1/4
(cell C1)


HTH explain

the closest in the screenshot would be

 

[T. Valko]

That's pretty complicated!

It might be easier to show you via a sample file:

Delete2.xls 14kb

http://cjoint.com/?gtuWauWdQh

Both formulas are array formulas**.

The formula that gets the row number is limited to an array that is <=5641
rows. If your table is bigger than that we'll need to find a different
approach.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

One thing to note, if there are 2 numbers that are equally spaced the
formulas will return the *first* matches. For example, if the lookup value
was 500 and in the table there is 450 and 550, the formulas will match 450.

Biff

 

[Chip Pearson]

You might try the "closest match lookups" section on my Lookups page.
http://www.cpearson.com/excel/lookups.htm about 2/3 of the way down.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Lori]

To return the Row number, try the array formula:

=MIN(IF((Rng-A1)^2=MIN((Rng-A1)^2),ROW(Rng)))

where Rng is the data range.

For the column number replace ROW by COLUMN above
The three return values are then:

=INDEX(Data!1:65536,Row,Column)
=INDEX(Data!A:A,Row)
=INDEX(Data!1:1,Column)

in which you can substitute the formulas for Row and Rolumn.

Note: There appears to be an overlap across some rows, and the second
example does have an exact match on row 16.

 

[T. Valko]

The combination of INDEX/MATCH/MMULT and INDEX/MATCH calculates about twice
as fast as INDEX/MIN/ROW and INDEX/MIN/COLUMN.

Avg of 5 calculations:

0.00056 vs 0.00108

Biff

 

[T. Valko]

The formula that gets the row number is limited to an array that is <=5641

rows

Correction. The limit is 5460. Technically, the limit is an output array
that contains no more than 5460 elements. In the context of my reply each
row is an element thus the limit would be <=5460 rows.

Biff

 

[ADK]

This works for my total table size.

Thanks!

Correction. The limit is 5460. Technically, the limit is an output array
that contains no more than 5460 elements. In the context of my reply each
row is an element thus the limit would be <=5460 rows.

Biff

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

This works for my total table size.

Thanks!