Complicated Lookup Function
 

i want to type a value into a cell and have a function that returns the
header of the column that has a value that is closest to the value I typed in
.. For example, you have a table and the first row is numbered 1 through 10
(column headers). The second row has values 5, 10, 15, etc in columns 1
through 10. I type in the value 12. I want the function to return "2"
(because 10 is closest to 12 and it is in column 2).

Complicated Lookup Function
 

Assuming data is rows 1 and 2, columns A to J, data in row 2 is ascending
order.

"Search" value in A3

=IF(MOD(A3,10)<3,INDEX($A$1:$J$1,MATCH(A3,$A$2:$J$ 2,1)),INDEX($A$1:$J$1,MATCH(A3,$A$2:$J$2,1)+1))

This assumes values like 13 are treated nearer to 15 i.e obey the standard
rounding up rules.

HTH

"Latika" wrote:

i want to type a value into a cell and have a function that returns the
header of the column that has a value that is closest to the value I typed in
. For example, you have a table and the first row is numbered 1 through 10
(column headers). The second row has values 5, 10, 15, etc in columns 1
through 10. I type in the value 12. I want the function to return "2"
(because 10 is closest to 12 and it is in column 2).

Complicated Lookup Function
 

Thanks, the problem is that the data is NOT ascending! it's actually a time
series so the values in row 2 do not all necessarily ascend. I just want
Excel to calculate the distance between the value I give it, and each of the
values in row 2 and then choose the value in row 2 that is closest to the
value i gave it...is there any way to do that?

"Toppers" wrote:

Assuming data is rows 1 and 2, columns A to J, data in row 2 is ascending
order.

"Search" value in A3

=IF(MOD(A3,10)<3,INDEX($A$1:$J$1,MATCH(A3,$A$2:$J$ 2,1)),INDEX($A$1:$J$1,MATCH(A3,$A$2:$J$2,1)+1))

This assumes values like 13 are treated nearer to 15 i.e obey the standard
rounding up rules.

HTH

"Latika" wrote:

i want to type a value into a cell and have a function that returns the
header of the column that has a value that is closest to the value I typed in
. For example, you have a table and the first row is numbered 1 through 10
(column headers). The second row has values 5, 10, 15, etc in columns 1
through 10. I type in the value 12. I want the function to return "2"
(because 10 is closest to 12 and it is in column 2).

Complicated Lookup Function
 

Hi!

A10 = input cell = 12

Formula entered as an array using the key combination of CTRL,SHIFT,ENTER:

=MATCH(MIN(ABS(A2:J2-A10)),ABS(A2:J2-A10),0)

Note: if there is more than one instance where the difference is equal the
formula will return the first instance.

Biff

"Latika" wrote in message
...
i want to type a value into a cell and have a function that returns the
header of the column that has a value that is closest to the value I typed
in
. For example, you have a table and the first row is numbered 1 through
10
(column headers). The second row has values 5, 10, 15, etc in columns 1
through 10. I type in the value 12. I want the function to return "2"
(because 10 is closest to 12 and it is in column 2).