In Excel, a 2D array has values along cell rows and columns. To find the location of any particular value in the 2D array, the SUMPRODUCT function should be used.
Formula
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
=SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
How to Get Location of Value in 2D Array
In order to get the location of a value in a 2d array, you need to find the position of that value along the row and across the column.
To achieve the desired result,
- Slot in the formula to get the row location
- Slot in the formula to get the column location
- Use the result to get the max location of the value in 2D array
Example
Consider a 2 by 2 matrix with 7 rows and 4 columns. If we need to find the largest value in that array, then we must first begin by identifying what row and what column contains the result that we seek.
- Write the content of the array
Figure 1 – Showing the 2D array – 4 by 7
- Create your data table
Figure 2 – Showing the data table row and column for result values
- Slot in the formula for the row
Figure 3 – Showing row formula
- Slot in the formula for the column location of the value in 2D array
Figure 4 – Showing the column formula
- Use the result to find the maximum value in the 2D array
Figure 5 – Result showing the row and column of the maximum value
Leave a Comment