Using INDEX and MATCH in an Array

[Wox]

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

 

[T. Valko]

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

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

 

[Luke M]

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

 

[Wox]

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?

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

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

.

 

[Peo Sjoblom]

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

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?

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

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

.

 

[T. Valko]

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

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?

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

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

.

 

[Wox]

Thanks guys,

Both solutions work great!
-wox

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

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?

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


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


.

.

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Thanks guys,

Both solutions work great!
-wox

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

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?

:

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


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


.

.