Find Value in matrix Table and return column and row names

M

MaiyaD

New Member
Joined
Apr 22, 2019
Messages
2
Hi! Can you please help me solve in getting the row and column name given the value from a two dimensional matrix table?



Here is the table I am trying to work on:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]1[/TD]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Team1[/TD]
[TD]Team2[/TD]
[TD]Team 3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Task 1[/TD]
[TD]Code1, Code 2[/TD]
[TD]Code3[/TD]
[TD]Code1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Task 2[/TD]
[TD]Code2[/TD]
[TD]Code1[/TD]
[TD]Code 2, Code 3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Task 3[/TD]
[TD]Code 3[/TD]
[TD]Code 2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Expected result:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Input value[/TD]
[TD]Task[/TD]
[TD]Team [/TD]
[/TR]
[TR]
[TD]Code1[/TD]
[TD]Task1[/TD]
[TD]Team1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Task 1[/TD]
[TD]Team 3[/TD]
[/TR]
</tbody>[/TABLE]


Any help would be greatly appreciated!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Welcome to the forums. Just to clarify, shouldn't Task 2 / Team 2 also be mentioned because it has Code 1 in it?
 
Upvote 0
Probably the quickest way would be to use Power Query.

Step 1) Make the below table (copy/paste into excel), then bring it into power query. Then right click 'Code1', then click 'drill down', then click 'home', then click 'close and load to' 'only create a connection'.

[TABLE="width: 76"]
<tbody>[TR]
[TD]Input Value[/TD]
[/TR]
[TR]
[TD]Code1[/TD]
[/TR]
</tbody>[/TABLE]

Step 2) Make the below table (copy/paste into excel), then bring it into power query.


[TABLE="width: 285"]
<tbody>[TR]
[TD]Task No.[/TD]
[TD]Team 1[/TD]
[TD]Team 2[/TD]
[TD]Team 3[/TD]
[/TR]
[TR]
[TD]Task 1[/TD]
[TD]Code1, Code2[/TD]
[TD]Code3[/TD]
[TD]Code1[/TD]
[/TR]
[TR]
[TD]Task 2[/TD]
[TD]Code2[/TD]
[TD]Code1[/TD]
[TD]Code2, Code3[/TD]
[/TR]
[TR]
[TD]Task 3[/TD]
[TD]Code3[/TD]
[TD]Code 2[/TD]
[TD]Code3[/TD]
[/TR]
</tbody>[/TABLE]

Step 3) Go to advanced editor and paste the below. Then click 'home, then 'close and load'.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task No.", type text}, {"Team 1", type text}, {"Team 2", type text}, {"Team 3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Task No."}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each Text.Contains([Value], Table1))
in
    #"Filtered Rows"

Note, you might need to alter the names of your tables/queries in order to make this work. Hope this helps...

... in the end, your spreadsheet should look something like this... and then you can just change the input value and then click refresh and everything will update. There are formulaic solutions, but i feel those would be quite complex, unless someone has an efficient idea or can provide VBA code. Good luck.

[TABLE="width: 714"]
<tbody>[TR]
[TD]Task No.[/TD]
[TD]Team 1[/TD]
[TD]Team 2[/TD]
[TD]Team 3[/TD]
[TD][/TD]
[TD]Input Value[/TD]
[TD][/TD]
[TD]Task No.[/TD]
[TD]Attribute[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Task 1[/TD]
[TD]Code1, Code2[/TD]
[TD]Code3[/TD]
[TD]Code1[/TD]
[TD][/TD]
[TD]Code1[/TD]
[TD][/TD]
[TD]Task 1[/TD]
[TD]Team 1[/TD]
[TD]Code1, Code2[/TD]
[/TR]
[TR]
[TD]Task 2[/TD]
[TD]Code2[/TD]
[TD]Code1[/TD]
[TD]Code2, Code3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Task 1[/TD]
[TD]Team 3[/TD]
[TD]Code1[/TD]
[/TR]
[TR]
[TD]Task 3[/TD]
[TD]Code3[/TD]
[TD]Code 2[/TD]
[TD]Code3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Task 2[/TD]
[TD]Team 2[/TD]
[TD]Code1[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
if you have the data in this way



Book1
ABCDE
1Team1Team2Team 3
2Task 1Code 1, Code 2Code3Code 1
3Task 2Code2Code 1Code 2, Code 3
4Task 3Code 3Code 2
5
6
7Input valueTaskTeam
8Code 1Task 1Team1
9Task 1Team 3
10Task 2Team2
Hoja1


Try with this macro

Code: 
Sub Find_Value()
    Dim r As Range, ivalue As Range, b As Range
    Dim cell As String, f As Long
    
    Set r = Range("B1:E4")
    Set ivalue = Range("A8")
    
    f = 0
    Set b = r.Find(ivalue.Value, LookIn:=xlValues, lookat:=xlPart)
    If Not b Is Nothing Then
        cell = b.Address
        Do
            ivalue.Offset(f, 1).Value = Cells(b.Row, "B")
            ivalue.Offset(f, 2).Value = Cells(1, b.Column)
            f = f + 1
            Set b = r.FindNext(b)
        Loop While Not b Is Nothing And b.Address <> cell
    End If
End Sub
 
Upvote 0
A solution with formulas

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[TD="bgcolor: #DCE6F1"]
I​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
Team1​
[/TD]
[TD]
Team2​
[/TD]
[TD]
Team 3​
[/TD]
[TD][/TD]
[TD]
Input value​
[/TD]
[TD]
Task​
[/TD]
[TD]
Team​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD][/TD]
[TD]
Task 1​
[/TD]
[TD]
Code1, Code2​
[/TD]
[TD]
Code3​
[/TD]
[TD]
Code1​
[/TD]
[TD][/TD]
[TD]
Code1​
[/TD]
[TD]
Task 1​
[/TD]
[TD]
Team1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD][/TD]
[TD]
Task 2​
[/TD]
[TD]
Code2​
[/TD]
[TD]
Code1​
[/TD]
[TD]
Code2, Code3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
Task 1​
[/TD]
[TD]
Team 3​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD][/TD]
[TD]
Task 3​
[/TD]
[TD]
Code3​
[/TD]
[TD]
Code2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Task 2​
[/TD]
[TD]
Team2​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5
​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in H2 copied down
=IFERROR(INDEX($B$2:$B$4,SMALL(IF(ISNUMBER(SEARCH($G$2,$C$2:$E$4)),ROW($C$2:$E$4)-ROW($C$2)+1),ROWS(H$2:H2))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

Array formula in I2 copied down
=IF(H2="","",INDEX($C$1:$E$1,SMALL(IF(ISNUMBER(SEARCH($G$2,$C$2:$E$4)),IF($B$2:$B$4=H2,COLUMN($C$2:$E$4)-COLUMN($C$2)+1)),COUNTIF(H$2:H2,H2))))
confirmed with Ctrl+Shift+Enter, not just Enter

M.
 
Last edited:
Upvote 0
Nice one Marcelo. I was writing something similar to begin with (using ISUMBER and SEARCH), but I didn't think of doing the whole array (C2:E4), that never occurred to me (didn't know it could be done). Nice one.
 
Upvote 0
CyrusTheVirus said:
Nice one Marcelo. I was writing something similar to begin with (using ISUMBER and SEARCH), but I didn't think of doing the whole array (C2:E4), that never occurred to me (didn't know it could be done). Nice one.
Click to expand...

Thank you. I really appreciated your comment :)

M.
 
Upvote 0
Hi Marcelo, Thank you for your feedback. It worked!

Marcelo Branco said:
A solution with formulas

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[TD="bgcolor: #DCE6F1"]
I​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
Team1​
[/TD]
[TD]
Team2​
[/TD]
[TD]
Team 3​
[/TD]
[TD][/TD]
[TD]
Input value​
[/TD]
[TD]
Task​
[/TD]
[TD]
Team​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD][/TD]
[TD]
Task 1​
[/TD]
[TD]
Code1, Code2​
[/TD]
[TD]
Code3​
[/TD]
[TD]
Code1​
[/TD]
[TD][/TD]
[TD]
Code1​
[/TD]
[TD]
Task 1​
[/TD]
[TD]
Team1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD][/TD]
[TD]
Task 2​
[/TD]
[TD]
Code2​
[/TD]
[TD]
Code1​
[/TD]
[TD]
Code2, Code3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
Task 1​
[/TD]
[TD]
Team 3​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD][/TD]
[TD]
Task 3​
[/TD]
[TD]
Code3​
[/TD]
[TD]
Code2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Task 2​
[/TD]
[TD]
Team2​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5
​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in H2 copied down
=IFERROR(INDEX($B$2:$B$4,SMALL(IF(ISNUMBER(SEARCH($G$2,$C$2:$E$4)),ROW($C$2:$E$4)-ROW($C$2)+1),ROWS(H$2:H2))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

Array formula in I2 copied down
=IF(H2="","",INDEX($C$1:$E$1,SMALL(IF(ISNUMBER(SEARCH($G$2,$C$2:$E$4)),IF($B$2:$B$4=H2,COLUMN($C$2:$E$4)-COLUMN($C$2)+1)),COUNTIF(H$2:H2,H2))))
confirmed with Ctrl+Shift+Enter, not just Enter

M.
Click to expand...
 
Upvote 0
Hi Marcelo.

Thank you so much for this post.
I was struggling with this situation and your formulas helped me a lot.
But, somehow, they don't work with my matrix table.
My table consists of alphanumeric values and these formulas only work (sort of) if I round the value to find to 2 decimals.

Are you willing to help me?

Best regards.

H.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Rolled up color coding
Replies
3
Views
232
felixstraube
F
F
  • Question Question
VBA code that finds a value in a col. then returns entire row
Replies
3
Views
119
Fudging
F
A
  • Solved
VBA code to extract a table
Replies
5
Views
166
Anthony47
A
S
  • Solved
VLOOKUP or Macro?
Replies
9
Views
811
DanteAmor
DanteAmor
L
  • Solved
Insert formulas to new lines in table
Replies
3
Views
140
Ltuk85
L

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top