Using INDEX and MATCH in an Array

  • Thread starter Thread starter Wox
  • Start date Start date
W

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
 
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))
Click to expand...

Very few people use the structured syntax when writing formulas. No one
knows how to read that!

--
Biff
Microsoft Excel MVP


Wox said:
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
Click to expand...
 
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))
 
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 said:
The portion that does not work is MATCH(114,$H$3:$U$14,0)
Click to expand...

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))
Click to expand...

Very few people use the structured syntax when writing formulas. No one
knows how to read that!

--
Biff
Microsoft Excel MVP


Wox said:
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
Click to expand...


.
Click to expand...
 
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 said:
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 said:
The portion that does not work is MATCH(114,$H$3:$U$14,0)
Click to expand...

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))
Click to expand...

Very few people use the structured syntax when writing formulas. No one
knows how to read that!

--
Biff
Microsoft Excel MVP


Wox said:
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
Click to expand...


.
Click to expand...
Click to expand...
 
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 said:
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 said:
The portion that does not work is MATCH(114,$H$3:$U$14,0)
Click to expand...

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))
Click to expand...

Very few people use the structured syntax when writing formulas. No one
knows how to read that!

--
Biff
Microsoft Excel MVP


Wox said:
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
Click to expand...


.
Click to expand...
Click to expand...
 
Thanks guys,

Both solutions work great!
-wox

T. Valko said:
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 said:
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 said:
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


.
Click to expand...
Click to expand...


.
Click to expand...
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Wox said:
Thanks guys,

Both solutions work great!
-wox

T. Valko said:
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 said:
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


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top