Finding the largest value in a 2-dimensional range and returning the column header

I've been struggling to find a solution to locating the maximal number within a 2-dimensional range and then looking up the column header that the maximal number falls into.

For example:

I'm looking for a formula that would lookup the maximal number in the range A2 to C5 (in this example 9669). Then lookup the column that that number falls into (in this example Person C), and then return that value.

Seems simply enough for a 1-dimensional range, but I can't find anything for dealing with 2-dimensions.

Any help would be greatly appreciated.

Solution 1:

As Microsoft itself states: "So, you want to become a real Excel power user? In that case, you need to know how to use array formulas".

The solution consists of three steps:

1. define a matrix that searches for the maximum value's,
2. transform that matrix to 1's and 0's,
3. multiply that matrix with a vector containing the column numbers.

Step 1: As an experiment, try to select a range equal to your table size, and your enter =<table range> = MAX(<table range>) and you confirm with CTRLSHIFTENTER (the default key for entering array formulas).

Step 2: The double minus operator converts the TRUE/FALSE values to 0's (for FALSE) and 1's (for TRUE).

Step 3: Use SUMPRODUCT and COLUMN to multiply the array and to create the vector with column numbers.

So your formula looks like: SUMPRODUCT(--( <table range> = MAX( <table range> )) * COLUMN( <table range> )) (of course, replace <table range> with the range or name of your table.

Note that this formula returns the absolute worksheet column number of the found maximum value. So, 5 for column E in your worksheet. If you want the relative number in your table, subtract by the column of the left-top cell of your table and add one. So your formula becomes: SUMPRODUCT(--( <table range> =MAX( <table range> )) * COLUMN( <table range> )) - COLUMN( <left top cell of table> ) + 1

Also note that this final formula uses array formula's, but is in itself not an array, but a single value. So to enter this formula, you can just click ENTER.

My working example: