Master Class: Dynamically Identify The Column And The Row For A Value In A Matrix

Need to dynamically find the position of a value in a matrix? I have came across this problem recently for the purposes of identification of some values in a huge market performance matrix. Please check my solution of this problem.


The source matrix is on Pic.1. It is simplified for simplicity.

Pic.1 The source matrix

Simple Case: One Value Only
This is the case when you need to detect the region and the category for one value only. Tackle this by:
- Add formula to detect the row of the value: insert a formula in the row above (or below) the matrix that contains a MATCH formula. In my example for the first column it is:
=IF(ISERROR(MATCH(Value_to_Find,D$39:D$55,0)),0,1)
where Value_to_Find is my value and the range D$39:D$55 is the first column (notice the range lock)
- Add formula to detect the column of the value: insert a formula in the column to the left (or to the right) the matrix that contains another MATCH formula. In my example for the first row it is:
=IF(ISERROR(MATCH(Value_to_Find,$D39:$P39,0)),0,1)
where Value_to_Find is my value and the range $D39:$H39 is the first row of data (again, notice the range lock)
- Get the region and the category by simple  VLOOKUP and HLOOKUP

Simple indeed. What about when there are more than one values to locate. I could extend the simple case to meet this requirement but it is getting too complicated and there is a more convenient way for that.

More Than One Value
This is my real requirement that asked to find the region and category of the 20 largest values in the matrix. The key for the solution is to present the matrix in a vector - a single column or a row as shown in the picture below.

Pic.2 Vectorized matrix

- Create a "vectorized" matrix
The table in Pic.2 has all the combinations row and column numbers for the source matrix. The Value column pulls the data out of the matrix by a simple INDEX. Vector Pos column has the consequtive number for the row. MATCH and other functions can now be used.

- Create a table with the values to detect. Please see a sample table on Pic.3
  

Pic.3 Table with results

- Find the position of the value in the vectorized matrix with MATCH - it column "Vector Pos" in this example.
- Find the row and column numbers by a VLOOKUP by using the leftmost column of the vectorized matrix. Remember that Row and Column there are the rows and columns in the source matrix
- Get the region with INDEX formula on the leftmost column of the matrix and the column number found on the previous step
- Get the category with INDEX formula on the topmost column of the matrix and the row number found on the previous step
- Do not forget to put a column to detect if the value occurs more than once in the matrix. I have done this in No Occ column with COUNTIF.
- Done :)


My matrix was relatively big - 17 rows by 30 columns and automation like this one is justified. This approach is justified with big matrices and/or in case of regular updates. If it were one-off big matrix I would stop to populating the "vectorized" matrix and simply sort it. The limitation of this approach is the size of the matrix as Excel has limited number of rows and columns. However, if the matrix is, say, greater than 100x100 probably Excel is not the right tool for the purpose. There are few other solutions of the problem but this one seems the most straight-forward and easy to implement one.