< Go Back

Get location of value in 2D array

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.

  1. Write the content of the array

Figure 1 – Showing the 2D array – 4 by 7

  1. Create your data table

Figure 2 – Showing the data table row and column for result values

  1. Slot in the formula for the row

Figure 3 – Showing row formula

  1. Slot in the formula for the column location of the value in 2D array

Figure 4 – Showing the column formula

  1. Use the result to find the maximum value in the 2D array

Figure 5 – Result showing the row and column of the maximum value

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar