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

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

How can I get my array formula to recognize correctly chose the 2nd ranking value for argument "k", when there are duplicates in the array?
Solved by I. F. in 18 mins
Hello there Im trying to get help. My 2d pie chart is not showing up on excel
Solved by G. W. in 27 mins
find match of Sheet1!B2 in array Assignments!B6:U36 and return value of same cell from array BLANK!B6:U36
Solved by S. J. in 12 mins

Leave a Comment

avatar