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