Improve company productivity with a Business Account.Sign Up

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

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
Asked:
I_play_with_DNA
  • 4
  • 2
2 Solutions
 
Wayne Taylor (webtubbs)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

Open in new window

0
 
Wayne Taylor (webtubbs)Commented:
The attached workbook shows the UDF in action.
sample_data.xlsm
0
 
Subodh Tiwari (Neeraj)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))))

Open in new window


For details, refer to the attached.
sample_data.xlsx
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Wayne Taylor (webtubbs)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
 
Subodh Tiwari (Neeraj)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
 
Wayne Taylor (webtubbs)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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now