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.

  • copy cell data from one sheet to another depending on matching column values in both sheets
  • Visual Studio 2017 can't debug Excel 2016 web add-in
  • Macro with Save Current date
  • problem with excell export
  • GET request API for updating a data source
  • Excel Formula - if values in columns all contain X then return
  • 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!

  • Time uploading from excel sheet to mysql
  • Storing numeric values in an Excel sheet
  • Excel VBA Sorting Function Outputs
  • Copy multiple worksheets into one
  • Python: open existing Excel file and count rows in sheet
  • VBA overlapping networkdays from dates with a condition
  • One Solution collect form web for “Using a dynamic range in a Index Match formula to return the max value”

    =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

    MS Excel Spreadsheet is the best Office Software, Excel VBA and Excel Formulas make Spreadsheet work faster.