Solved

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

Posted on 2016-08-16
7
35 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 29

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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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 29

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

808 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