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.
All articles LOOKUP Get location of value in 2D array

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:
Solution examples
i have a table with Dates, First names , Last names and i want to count how many times a name occurs, but if a name occurs more than once on a particular date i need to only count it once. this is on excel and not google sheets
Solved by F. H. in 31 mins
I need cell B2 Green if cells G2:Q2 are empty. If cells G2:Q2 have any text in any cell then i need B2 to be red.
Solved by C. W. in 20 mins
Hi - I need a formula that can look at a Member Number (Column A) and report back if location (Column B) matches 1 of 6 locations, then looks to the date of occurance (Column N) but only adds back 1 value of repeating dates. I have this, but it is not coming back with the right answer... =SUMPRODUCT(('2017'!I$3:I$10000=$A223)*ISNUMBER(SEARCH({"2","6","58","69","76","97"},'2017'!N$3:N$10000))) Information is corporate private, so I won't be uploading file.
Solved by S. L. in 60 mins
hi, i have this formula =SUMPRODUCT((B4:B464<>"")/COUNTIF(B4:B464,B4:B464&"")), i want to add a subtotal to it. so when I filter the column it also sums up the unique number if enteries i have filtered on.
Solved by F. L. in 60 mins
Hi, I need a formula to work out how many values are less than a certain time value, simple "countif" do not work as it is a filtered spreadsheet. Here's the formula that I have got so far: SUMPRODUCT(SUBTOTAL(3,OFFSET(Current_Month!N$2:N$10000,ROW(Current_Month!N$2:N$10000)-MIN(ROW(Current_Month!N$2:N$10000)),,1)),--(Current_Month!N$2:N$10000>"24:0:0")
Solved by K. L. in 30 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

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.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc