Exceljet

Quick, clean, and to the point

Get location of value in 2D array

Excel formula: Get location of value in 2D array
Generic formula 
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Explanation 

To locate the position of a value in a 2D array, you can use the SUMPRODUCT function. In the example shown, the formulas used to locate the row and column numbers of the max value in the array are:

=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
=SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1

where "data" is the named range C5:G14.

Note: these formulas will fail if there are duplicate values in the array.

How these formula work

To get the row number, the data is compared to the max value, which generates an array of TRUE FALSE results. These are multiplied by the result of ROW (data) which generates and array of row numbers associated with the named range "data":

=SUMPRODUCT({FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE}*{5;6;7;8;9;10;11})

The multiplication operation causes Excel to coerce the TRUE FALSE values in the first array to 1s and 0s, so we can visualize an intermediate step like this:

=SUMPRODUCT({0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,1,0,0;0,0,0,0,0;0,0,0,0,0}*{5;6;7;8;9;10;11})

SUMPRODUCT then returns a result of 9, which corresponds to the 9th row on the worksheet. To get an index relative to the named range "data", we use:

-ROW(data)+1

The final result is the array {5;4;3;2;1;0;-1}, from which only the first value (5) is displayed.

The formula to determine the column position works in the same way.

Note: I ran into this approach in a comment by Mike Erickson over on MrExcel.com. There are some other good ideas in that thread as well, including an array formula option.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables