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:

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

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

  • Excel Formula to fetch data from corresponding cells of a search result
  • Excel LOOKUP of % character doesn't work
  • Excel VBA Open a Folder
  • How to filter Outlook mail items received by today from other mail items in Excel VBA
  • For Each Loop Causing Excel to Hang
  • {=Table(,B8)} Formula?
  • 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
    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!

  • Excel VBA - Read & Output Troubleshooting
  • How to replace comma separated values to comma separated ids in the same/individual cell in excel?
  • Excel FormulaArray Function Across a Table
  • How to count up text of a different font colour in excel
  • Issue with Excel VBA crashing constantly
  • Changing decimal separator in VBA (not only in Excel)
  • One Solution collect form web for “Using a dynamic range in a Index Match formula to return the max value”


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


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


    which is here equivalent to:


    See here for details of this property of INDEX.

    You can also refer to the whole worksheet using:


    in place of:


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


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