|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
November 10th, 2009, 06:36 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Using INDEX and MATCH in an Array
I am using the following formula on a table to return the item in the first
column where the MAX from a portion of the table is located. =INDEX(Table1[Team],MATCH(MAX(Table1[[1]:[14]],Table1[[1]:[14]],0),1)) The portion that does not work is MATCH(114,$H$3:$U$14,0) Can Match not be used to return the row for the index function when looking up multiple rows and columns? If not, what formula should I be using here? Thanks 
|
|
#2
November 11th, 2009, 01:45 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using INDEX and MATCH in an Array
The portion that does not work is MATCH(114,$H$3:$U$14,0)
The lookup_array $H$3:$U$14 *must* be a one dimensional array, a single row or single column. =INDEX(Table1[Team],MATCH(MAX(Table1[[1]:[14]],Table1[[1]:[14]],0),1)) Very few people use the structured syntax when writing formulas. No one knows how to read that! -- Biff Microsoft Excel MVP "Wox" wrote in message ... I am using the following formula on a table to return the item in the first column where the MAX from a portion of the table is located. =INDEX(Table1[Team],MATCH(MAX(Table1[[1]:[14]],Table1[[1]:[14]],0),1)) The portion that does not work is MATCH(114,$H$3:$U$14,0) Can Match not be used to return the row for the index function when looking up multiple rows and columns? If not, what formula should I be using here? Thanks
|
|
#3
November 11th, 2009, 03:23 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using INDEX and MATCH in an Array
At first glance, it appears your parenthesis are in the wrong place.
Corrected: =INDEX(Table1[Team],MATCH(MAX(Table1[[1]:[14]]),Table1[[1]:[14]],0)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Wox" wrote: I am using the following formula on a table to return the item in the first column where the MAX from a portion of the table is located. =INDEX(Table1[Team],MATCH(MAX(Table1[[1]:[14]],Table1[[1]:[14]],0),1)) The portion that does not work is MATCH(114,$H$3:$U$14,0) Can Match not be used to return the row for the index function when looking up multiple rows and columns? If not, what formula should I be using here? Thanks
|
|
#4
November 11th, 2009, 05:40 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using INDEX and MATCH in an Array
Thanks for the replys,
Here is the formula without table syntax: =INDEX(A3:A14,MATCH(MAX(H3:U14),H3:U14,0),1) What formula(s) can be used to select the row for the INDEX function from a 2d array? "T. Valko" wrote: The portion that does not work is MATCH(114,$H$3:$U$14,0) The lookup_array $H$3:$U$14 *must* be a one dimensional array, a single row or single column. =INDEX(Table1[Team],MATCH(MAX(Table1[[1]:[14]],Table1[[1]:[14]],0),1)) Very few people use the structured syntax when writing formulas. No one knows how to read that! -- Biff Microsoft Excel MVP "Wox" wrote in message ... I am using the following formula on a table to return the item in the first column where the MAX from a portion of the table is located. =INDEX(Table1[Team],MATCH(MAX(Table1[[1]:[14]],Table1[[1]:[14]],0),1)) The portion that does not work is MATCH(114,$H$3:$U$14,0) Can Match not be used to return the row for the index function when looking up multiple rows and columns? If not, what formula should I be using here? Thanks .
|
|
#5
November 11th, 2009, 06:00 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using INDEX and MATCH in an Array
This will work
=INDEX(A1:A14,MAX(IF(H3:U14=MAX(H3:U14),ROW(H3:U14 )))) entered with ctrl + shift & enter NOTE that you need to start at A1 because the ROW() function counts from the first row, either use A1:A14 or offset the MAX result by the first 2 non included rows like =INDEX(A3:A14,MAX(IF(H3:U14=MAX(H3:U14),ROW(H3:U14 )))-ROWS(A1:A2)) -- Regards, Peo Sjoblom "Wox" wrote in message ... Thanks for the replys, Here is the formula without table syntax: =INDEX(A3:A14,MATCH(MAX(H3:U14),H3:U14,0),1) What formula(s) can be used to select the row for the INDEX function from a 2d array? "T. Valko" wrote: The portion that does not work is MATCH(114,$H$3:$U$14,0) The lookup_array $H$3:$U$14 *must* be a one dimensional array, a single row or single column. =INDEX(Table1[Team],MATCH(MAX(Table1[[1]:[14]],Table1[[1]:[14]],0),1)) Very few people use the structured syntax when writing formulas. No one knows how to read that! -- Biff Microsoft Excel MVP "Wox" wrote in message ... I am using the following formula on a table to return the item in the first column where the MAX from a portion of the table is located. =INDEX(Table1[Team],MATCH(MAX(Table1[[1]:[14]],Table1[[1]:[14]],0),1)) The portion that does not work is MATCH(114,$H$3:$U$14,0) Can Match not be used to return the row for the index function when looking up multiple rows and columns? If not, what formula should I be using here? Thanks .
|
|
#6
November 11th, 2009, 06:20 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using INDEX and MATCH in an Array
As long as there is only one instance of MAX in H3:U14...
In the formula, Table refers to H3:U14. Array entered** : =INDEX(A3:A14,MAX((Table=MAX(Table))*ROW(Table))-MIN(ROW(Table))+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Or, you could add a column to the table that identifies the row that contains the MAX value, say, column V. Enter this formula in V3 and copy down to V14: =IF(COUNTIF(H3:U3,MAX(Table)),"x","") Then: =INDEX(A3:A14,MATCH("x",V3:V14,0)) -- Biff Microsoft Excel MVP "Wox" wrote in message ... Thanks for the replys, Here is the formula without table syntax: =INDEX(A3:A14,MATCH(MAX(H3:U14),H3:U14,0),1) What formula(s) can be used to select the row for the INDEX function from a 2d array? "T. Valko" wrote: The portion that does not work is MATCH(114,$H$3:$U$14,0) The lookup_array $H$3:$U$14 *must* be a one dimensional array, a single row or single column. =INDEX(Table1[Team],MATCH(MAX(Table1[[1]:[14]],Table1[[1]:[14]],0),1)) Very few people use the structured syntax when writing formulas. No one knows how to read that! -- Biff Microsoft Excel MVP "Wox" wrote in message ... I am using the following formula on a table to return the item in the first column where the MAX from a portion of the table is located. =INDEX(Table1[Team],MATCH(MAX(Table1[[1]:[14]],Table1[[1]:[14]],0),1)) The portion that does not work is MATCH(114,$H$3:$U$14,0) Can Match not be used to return the row for the index function when looking up multiple rows and columns? If not, what formula should I be using here? Thanks .
|
|
#7
November 11th, 2009, 07:30 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using INDEX and MATCH in an Array
Thanks guys,
Both solutions work great! -wox "T. Valko" wrote: As long as there is only one instance of MAX in H3:U14... In the formula, Table refers to H3:U14. Array entered** : =INDEX(A3:A14,MAX((Table=MAX(Table))*ROW(Table))-MIN(ROW(Table))+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Or, you could add a column to the table that identifies the row that contains the MAX value, say, column V. Enter this formula in V3 and copy down to V14: =IF(COUNTIF(H3:U3,MAX(Table)),"x","") Then: =INDEX(A3:A14,MATCH("x",V3:V14,0)) -- Biff Microsoft Excel MVP "Wox" wrote in message ... Thanks for the replys, Here is the formula without table syntax: =INDEX(A3:A14,MATCH(MAX(H3:U14),H3:U14,0),1) What formula(s) can be used to select the row for the INDEX function from a 2d array? "T. Valko" wrote: The portion that does not work is MATCH(114,$H$3:$U$14,0) The lookup_array $H$3:$U$14 *must* be a one dimensional array, a single row or single column. =INDEX(Table1[Team],MATCH(MAX(Table1[[1]:[14]],Table1[[1]:[14]],0),1)) Very few people use the structured syntax when writing formulas. No one knows how to read that! -- Biff Microsoft Excel MVP "Wox" wrote in message ... I am using the following formula on a table to return the item in the first column where the MAX from a portion of the table is located. =INDEX(Table1[Team],MATCH(MAX(Table1[[1]:[14]],Table1[[1]:[14]],0),1)) The portion that does not work is MATCH(114,$H$3:$U$14,0) Can Match not be used to return the row for the index function when looking up multiple rows and columns? If not, what formula should I be using here? Thanks . .
|
|
#8
November 11th, 2009, 09:08 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using INDEX and MATCH in an Array
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Wox" wrote in message ... Thanks guys, Both solutions work great! -wox "T. Valko" wrote: As long as there is only one instance of MAX in H3:U14... In the formula, Table refers to H3:U14. Array entered** : =INDEX(A3:A14,MAX((Table=MAX(Table))*ROW(Table))-MIN(ROW(Table))+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Or, you could add a column to the table that identifies the row that contains the MAX value, say, column V. Enter this formula in V3 and copy down to V14: =IF(COUNTIF(H3:U3,MAX(Table)),"x","") Then: =INDEX(A3:A14,MATCH("x",V3:V14,0)) -- Biff Microsoft Excel MVP "Wox" wrote in message ... Thanks for the replys, Here is the formula without table syntax: =INDEX(A3:A14,MATCH(MAX(H3:U14),H3:U14,0),1) What formula(s) can be used to select the row for the INDEX function from a 2d array? "T. Valko" wrote: The portion that does not work is MATCH(114,$H$3:$U$14,0) The lookup_array $H$3:$U$14 *must* be a one dimensional array, a single row or single column. =INDEX(Table1[Team],MATCH(MAX(Table1[[1]:[14]],Table1[[1]:[14]],0),1)) Very few people use the structured syntax when writing formulas. No one knows how to read that! -- Biff Microsoft Excel MVP "Wox" wrote in message ... I am using the following formula on a table to return the item in the first column where the MAX from a portion of the table is located. =INDEX(Table1[Team],MATCH(MAX(Table1[[1]:[14]],Table1[[1]:[14]],0),1)) The portion that does not work is MATCH(114,$H$3:$U$14,0) Can Match not be used to return the row for the index function when looking up multiple rows and columns? If not, what formula should I be using here? Thanks . .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
