| Home |
| Search |
| Today's Posts |
|
#1
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
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
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
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
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
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
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
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 . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array formula with index + match | Excel Worksheet Functions | |||
| Help with an Index Match Array | Excel Worksheet Functions | |||
| index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
| Index and Match Array formula | Excel Worksheet Functions | |||
| Array index, match problem | Excel Worksheet Functions | |||
Linear Mode
