CODEWITHSUNDEEP
excelvlookup
will1329
will1329

Reputation: 173

Using a dynamic range in a Index Match formula to return the max value

I have got the following formula to return the maximum value/its area from my data: =INDEX($1:$1,0,MATCH(MAX(2:2),2:2,0)) 

  |Col A  | Col B  | Col C  | ETC.
1 |       | Area 1 | Area 2 |
2 |Topic1 | 50.57  | 60.36  |
3 |Topic2 | 467.8  | 636.8  |
ETC.

In this case the formula would return 60.36 / Area 2 depending on the row used in the Index function.

However, there is no guarantee that all of the topics will be present so I want to use a dynamic row reference rather than fixing it at e.g. 2:2 - i.e. instead of simply 2:2 it would look for Topic1, find the maximum value in that row and return either the area or the value (I'll need both).

I've tried using something like
=INDEX($1:$1,0,MATCH(MAX(MATCH("Topic1",A:A):MATCH("Topic1",A:A)),(MATCH("Topic1",A:A):MATCH("Topic1",A:A)),0))
without success.

I strongly suspect I'm missing something obvious but any help would be appreciated.

Thanks in advance.

EDIT: Sort of answered my own question. In case it helps anyone else I reorganized my data in a better format (each row: Topic1|Area1|Value) and used =MAXIFS(C:C,A:A,F1) to return the value and =INDEX(B:B,MATCH(MAXIFS(C:C,A:A,A1),C:C,0)) to return the area name.

EDIT2: Can confirm that XOR LX solution also works - thanks!

Upvotes: 1

Views: 4749

Answers (1)

XOR LX
XOR LX

Reputation: 7742

=INDEX($1:$1,0,MATCH(MAX(INDEX($1:$1048576,MATCH("Topic1",A:A,0),0)),INDEX($1:$1048576,MATCH("Topic1",A:A,0),0),0))

The use of zero as the column_num parameter within the part:

INDEX($1:$1048576,MATCH("Topic1",A:A,0),0)

forces that function to return a reference to the entire row. That is, in this case, the above resolves to:

INDEX($1:$1048576,2,0)

which is here equivalent to:

$2:$2

See here for details of this property of INDEX.

You can also refer to the whole worksheet using:

$A:$XFD

in place of:

$1:$1048576

though Excel will in any case convert it to the latter once the formula is committed.

Regards

Upvotes: 2

Related Questions