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

"Lori" wrote in message
oups.com...
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.


On 19 Jun, 18:45, "ADK" wrote:
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"
wrote in message

...

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" wrote in message
...
sorry, looked better before sending....hopefully this image is ok

"ADK" wrote in message
.. .
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" wrote in message
...
Is your list of numbers sorted? In what order, ascending or
descending?
You want the closest whether it's over or under?

Biff

"ADK" wrote in message
.. .
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?