Solved

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

Posted on 2016-08-16
7
48 Views
Last Modified: 2016-09-05
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
Comment
Question by:I_play_with_DNA
  • 4
  • 2
7 Comments
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 250 total points (awarded by participants)
ID: 41758740
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
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41758742
The attached workbook shows the UDF in action.
sample_data.xlsm
0
 
LVL 30

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 250 total points (awarded by participants)
ID: 41758871
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41778943
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
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41779192
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
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41784459
Both options have merit, so I propose an equal split.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question