Solved

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

Posted on 2016-08-16
27 Views
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
Question by:I_play_with_DNA
• 4
• 2

LVL 47

Accepted Solution

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
``````
0

LVL 47

Expert Comment

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

LVL 28

Assisted Solution

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))))
``````

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

LVL 47

Expert Comment

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 28

Expert Comment

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

ID: 41784459
Both options have merit, so I propose an equal split.
0

## Featured Post

### Suggested Solutions

excel file 5 44
Name Rotation 11 27
Top 10 with duplicates in Excel Table 4 10
Macro to delete column 3 8
A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

#### Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!