Find Value in matrix Table and return column and row names

[MaiyaD]

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:

1		C	D	E
2		Team1	Team2	Team 3
3	Task 1	Code1, Code 2	Code3	Code1
4	Task 2	Code2	Code1	Code 2, Code 3
5	Task 3	Code 3	Code 2

<tbody>
</tbody>

Expected result:

Input value	Task	Team
Code1	Task1	Team1
	Task 1	Team 3

<tbody>
</tbody>

Any help would be greatly appreciated!

 

[CyrusTheVirus]

Welcome to the forums. Just to clarify, shouldn't Task 2 / Team 2 also be mentioned because it has Code 1 in it?

 

[CyrusTheVirus]

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'.

Input Value

Code1

<tbody>
</tbody>

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

Task No.	Team 1	Team 2	Team 3
Task 1	Code1, Code2	Code3	Code1
Task 2	Code2	Code1	Code2, Code3
Task 3	Code3	Code 2	Code3

<tbody>
</tbody>

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

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.

Task No.	Team 1	Team 2	Team 3		Input Value		Task No.	Attribute	Value
Task 1	Code1, Code2	Code3	Code1		Code1		Task 1	Team 1	Code1, Code2
Task 2	Code2	Code1	Code2, Code3				Task 1	Team 3	Code1
Task 3	Code3	Code 2	Code3				Task 2	Team 2	Code1

<tbody>
</tbody>

 

[DanteAmor]

if you have the data in this way

Book1
	A	B	C	D	E
1			Team1	Team2	Team 3
2		Task 1	Code 1, Code 2	Code3	Code 1
3		Task 2	Code2	Code 1	Code 2, Code 3
4		Task 3	Code 3	Code 2
5
6
7	Input value	Task	Team
8	Code 1	Task 1	Team1
9		Task 1	Team 3
10		Task 2	Team2
Hoja1

Try with this macro

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

 

[Marcelo Branco]

A solution with formulas

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​			Team1​	Team2​	Team 3​		Input value​	Task​	Team​
2​		Task 1​	Code1, Code2​	Code3​	Code1​		Code1​	Task 1​	Team1​
3​		Task 2​	Code2​	Code1​	Code2, Code3​			Task 1​	Team 3​
4​		Task 3​	Code3​	Code2​				Task 2​	Team2​
5 ​

<tbody>
</tbody>

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.

 

[CyrusTheVirus]

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.

 

[Marcelo Branco]

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.

Thank you. I really appreciated your comment

M.

 

[MaiyaD]

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

A solution with formulas

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​			Team1​	Team2​	Team 3​		Input value​	Task​	Team​
2​		Task 1​	Code1, Code2​	Code3​	Code1​		Code1​	Task 1​	Team1​
3​		Task 2​	Code2​	Code1​	Code2, Code3​			Task 1​	Team 3​
4​		Task 3​	Code3​	Code2​				Task 2​	Team2​
5 ​

<tbody>
</tbody>

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.

 

[Marcelo Branco]

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

You're welcome. Glad to help.

M.

 

[starlightmuse]

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.