Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

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

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. 