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