Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Get location of value in 2D array in Excel

This tutorial shows how to Get location of value in 2D array in Excel using the example below;




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:


where “data” is the named range C5:G14.

Worked Example:   Count numbers that begin with in Excel

Note: for this example, we are arbitrarily find the location of the maximum value in the data, but you can replace data=MAX(data) with any other logical test that will isolate a given value.  Also 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”:


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 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:


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

Worked Example:   Get days, hours, and minutes between dates in Excel

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

Worked Example:   How to strip non-numeric characters in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *