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

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

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

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" <(E-Mail Removed)> wrote in message
news:8F366B33-DF84-421D-9141-(E-Mail Removed)...
>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).