x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 116

# Excel - Lookup function to return the horizontal and vertical coordinate of a particular cell in the matrix

The following is in the attached Excel file.

I have a matrix of data values that is 12 cells wide by 8 cells long. Rows in the matrix re labelled with the letters A-H and columns are labelled with the numbers 1-12. Note that these labels are NOT the Excel row and column IDs; they are labels that are typed into cells.

As an example, sample 111-L1 (which is in Excel cell D5 in this example) has the coordinate of A1 in the matrix. What I am trying to do is create a lookup function that searches the matrix for a specific string (for example, 111-L1) and returns the matrix coordinates of the cell that contains that string. So in this case, the function should return 'A1'. I can use VLOOKUP when all the data is in rows, but I'm at a loss to make this work when the data is actually in 2 dimensions and the lookup result is the combination of information from two specific cells.

Other important info/constraints:
1. The matrix will always be the size in the example: 12 columns x 8 rows, but may not always be in the same position on the worksheet.
2. The row and column labels (the A-H and 1-12) could change, so they actually need to be looked up rather than hardcoded based on relative position.
3. Ignore the "index" numbers (the Nxxx and Sxxx numbers); they are not static.
sample_data.xlsx
0
I_play_with_DNA
• 4
• 2
2 Solutions

Commented:
Are you open to a VBA function to do this? It is possible to do with a formula, but it will be unsightly and difficult to maintain. This UDF (User Defined Function) will return the matrix coordinates of the requested ID....

``````Public Function MatrixCoord(ID As String, Matrix As Range)
'   ID = The ID to lookup within the Matrix
'   Matrix = The range of cells to look for the ID. Includes the Row and Column headers

Dim fndIDCell As Range

Set fndIDCell = Matrix.Find(ID, Matrix.Cells(1, 1), xlValues, xlWhole)
If fndIDCell Is Nothing Or ID = "" Then
MatrixCoord = CVErr(xlErrNA)
Else
MatrixCoord = Matrix.Cells(fndIDCell.Row - Matrix.Row + 1, 1) & Matrix.Cells(1, fndIDCell.Column - Matrix.Column + 1)
End If

End Function
``````
0

Commented:
The attached workbook shows the UDF in action.
sample_data.xlsm
0

Excel & VBA ExpertCommented:
Assuming your lookup value is in C15, then try this....

In D15
``````=INDEX(\$C\$5:\$C\$12,SUMPRODUCT((ISNUMBER(SEARCH(C15,\$D\$5:\$O\$12))*(ROW(\$C\$5:\$C\$12)-ROW(\$C\$5)+1))))&INDEX(\$D\$4:\$O\$4,SUMPRODUCT((ISNUMBER(SEARCH(C15,\$D\$5:\$O\$12))*(COLUMN(\$D\$4:\$O\$4)-COLUMN(\$D\$4)+1))))
``````

For details, refer to the attached.
sample_data.xlsx
0

Commented:
Neeraj,

It doesn't actually mention a requirement for a FORMULA anywhere, so your argument is moot. My suggestion is much easier to manage and modify when using different ranges.

I propose an equal split.

Wayne
0

Excel & VBA ExpertCommented:
Wayne,

My closing request was based on the fact that OP required a formula to get the desired output.
I know your solution would work but I took it as an alternative solution.

But that's completely fine if you think so. I  have no objection.

Cheers
0

Commented:
Both options have merit, so I propose an equal split.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.