Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Function help | Excel Discussion (Misc queries) | |||
Lookup Function Problems | Excel Worksheet Functions | |||
Complicated lookup function | Excel Worksheet Functions | |||
lookup function 1 | Excel Worksheet Functions | |||
How do I use 3 cells to create the string for a lookup function? | Excel Worksheet Functions |