| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX() columns first
I want to use INDEX() or something similar to fiind a position of a value. I need to reference the column first. INDEX() looks at rows first. I really don't want to rearrange my table as it is 20 wide by 1000 long. Is there another solution? -- spxer ------------------------------------------------------------------------ spxer's Profile: http://www.excelforum.com/member.php...o&userid=37025 View this thread: http://www.excelforum.com/showthread...hreadid=568648 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX() columns first
The Match() function returns positions.
Why not post some examples of what you're trying to accomplish? -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "spxer" wrote in message ... I want to use INDEX() or something similar to fiind a position of a value. I need to reference the column first. INDEX() looks at rows first. I really don't want to rearrange my table as it is 20 wide by 1000 long. Is there another solution? -- spxer ------------------------------------------------------------------------ spxer's Profile: http://www.excelforum.com/member.php...o&userid=37025 View this thread: http://www.excelforum.com/showthread...hreadid=568648 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX() columns first
K L M N O P ............. 1 100 200 300 400 500 2 1900 1900 1900 1900 1900 3 19240 19480 19720 19960 20200 4 19600 20200 20800 21400 22000 L1:AE1 is the table index headers L2:AE1000 is the values to compare to K will compare a value from H to L1:AE1 to determine the column and then K will compare a value from I to the appropriate column to find the nearest number and return its row(2,3,4etc.) I looked at Index(array,Match(),Match()), but Index looks for row first and I need to establish column first. -- spxer ------------------------------------------------------------------------ spxer's Profile: http://www.excelforum.com/member.php...o&userid=37025 View this thread: http://www.excelforum.com/showthread...hreadid=568648 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX() columns first
sorry the message box misaligned my excel example. I hope it still makes sence -- spxer ------------------------------------------------------------------------ spxer's Profile: http://www.excelforum.com/member.php...o&userid=37025 View this thread: http://www.excelforum.com/showthread...hreadid=568648 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX() columns first
This may be better __K__ L____ M ____N ____O ____P ............. 1___ 100___200_ _300 __400 __500 2____1900__1900 _1900 _1900 _1900 3____19240_19480 19720 19960 20200 4____19600_20200 20800 21400 22000 L1:AE1 is the table index headers L2:AE1000 is the values to compare to K will compare a value from H to L1:AE1 to determine the column and then K will compare a value from I to the appropriate column to find the nearest number and return its row(2,3,4etc.) I looked at Index(array,Match(),Match()), but Index looks for row first and I need to establish column first._ -- spxer ------------------------------------------------------------------------ spxer's Profile: http://www.excelforum.com/member.php...o&userid=37025 View this thread: http://www.excelforum.com/showthread...hreadid=568648 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX() columns first
It all depends on how you use it, if for instance K2 tells which column to
look in then use it like =INDEX(Table,Match(),K2) Otherwise you need to come up with a better explanation with a concrete example -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "spxer" wrote in message ... This may be better __K__ L____ M ____N ____O ____P ............. 1___ 100___200_ _300 __400 __500 2____1900__1900 _1900 _1900 _1900 3____19240_19480 19720 19960 20200 4____19600_20200 20800 21400 22000 L1:AE1 is the table index headers L2:AE1000 is the values to compare to K will compare a value from H to L1:AE1 to determine the column and then K will compare a value from I to the appropriate column to find the nearest number and return its row(2,3,4etc.) I looked at Index(array,Match(),Match()), but Index looks for row first and I need to establish column first._ -- spxer ------------------------------------------------------------------------ spxer's Profile: http://www.excelforum.com/member.php...o&userid=37025 View this thread: http://www.excelforum.com/showthread...hreadid=568648 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX() columns first
__K____L____ _M ____N ____O ____P ............. 1____100___200_ _300 __400 __500 2____19000_19000_19000_19000_19000 3____19240_19480_19720_19960_20200 4____19600_20200_20800_21400_22000 I have a value in H3 of 251. I have a value in I3 of 19492. The correct column to find is M. The correct row is 3. The result I wish to return is 3. Suggestions? -- spxer ------------------------------------------------------------------------ spxer's Profile: http://www.excelforum.com/member.php...o&userid=37025 View this thread: http://www.excelforum.com/showthread...hreadid=568648 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX() columns first
__K____L____ _M ____N ____O ____P ............. 1____100___200_ _300 __400 __500 2____19000_19000_19000_19000_19000 3____19240_19480_19720_19960_20200 4____19600_20200_20800_21400_22000 I have a value in H3 of 251. I have a value in I3 of 19492. The correct column to find is M. The correct row is 3. The result I wish to return is 3. Suggestions? -- spxer ------------------------------------------------------------------------ spxer's Profile: http://www.excelforum.com/member.php...o&userid=37025 View this thread: http://www.excelforum.com/showthread...hreadid=568648 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX() columns first
This will return 3
=MATCH($I$3,INDEX($L$1:$P$4,,MATCH($H$3,$L$1:$P$1, 1)),1) if you want to return what's in the cell you need to build a bit more =INDEX($L$1:$P$4,MATCH($I$3,INDEX($L$1:$P$4,,MATCH ($H$3,$L$1:$P$1,1)),1),MATCH($H$3,$L$1:$P$1,1)) you can shorten it using offset however then the formula will be volatile -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "spxer" wrote in message ... __K____L____ _M ____N ____O ____P ............. 1____100___200_ _300 __400 __500 2____19000_19000_19000_19000_19000 3____19240_19480_19720_19960_20200 4____19600_20200_20800_21400_22000 I have a value in H3 of 251. I have a value in I3 of 19492. The correct column to find is M. The correct row is 3. The result I wish to return is 3. Suggestions? -- spxer ------------------------------------------------------------------------ spxer's Profile: http://www.excelforum.com/member.php...o&userid=37025 View this thread: http://www.excelforum.com/showthread...hreadid=568648 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDEX() columns first
Note that I assumed that your values are sorted in ascending order like in
your example -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "Peo Sjoblom" wrote in message ... This will return 3 =MATCH($I$3,INDEX($L$1:$P$4,,MATCH($H$3,$L$1:$P$1, 1)),1) if you want to return what's in the cell you need to build a bit more =INDEX($L$1:$P$4,MATCH($I$3,INDEX($L$1:$P$4,,MATCH ($H$3,$L$1:$P$1,1)),1),MATCH($H$3,$L$1:$P$1,1)) you can shorten it using offset however then the formula will be volatile -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "spxer" wrote in message ... __K____L____ _M ____N ____O ____P ............. 1____100___200_ _300 __400 __500 2____19000_19000_19000_19000_19000 3____19240_19480_19720_19960_20200 4____19600_20200_20800_21400_22000 I have a value in H3 of 251. I have a value in I3 of 19492. The correct column to find is M. The correct row is 3. The result I wish to return is 3. Suggestions? -- spxer ------------------------------------------------------------------------ spxer's Profile: http://www.excelforum.com/member.php...o&userid=37025 View this thread: http://www.excelforum.com/showthread...hreadid=568648 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Merging two columns and keeping the data from both | Excel Worksheet Functions | |||
| Help with grouping columns | New Users to Excel | |||
| cell color index comparison | New Users to Excel | |||
| Hiding columns and custom views problem | Excel Discussion (Misc queries) | |||
| Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
Linear Mode
