Forum Discussion
Find values depending on searched value in chart
=INDEX(A8:A31,SUM(MMULT(N(TRANSPOSE(B8:K31=M7)),ROW(1:24))))
=INDEX(B7:K7,SUM(MMULT(N(B8:K31=M7),ROW(1:10))))
I work with german Excel as well. These formulas work in my sample file if i want to search in range B8:K31. Please remember that the values in the search range must be unique for the formula to work. This means that the formula doesn't work for search value 385 because this value is in cell K9 and in cell H23 and therefore it's not unique.
For search range B8:K26 you can use this:
=INDEX(A8:A26,SUM(MMULT(N(TRANSPOSE(B8:K26=M7)),ROW(1:19))))
Please enter the formulas as arrayformulas with Ctrl+Shift+Enter if you work with legacy Excel.
In my sample file i didn't use exactly your values because it's too much typing.
A possible solution could be Power Query. In the attached file you can add data to the large blue dynamic table and choose a search value in the small blue table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
The data layout in the screenshot and in the attached file is for illustration. You can place the green result table (and the blue tables) anywhere in this or in another worksheet.
OliverScheurich hey, sorry for the late answer.
i've tried to implement (copy your sheet into my sheets) the PowerQuery version.
On my sheet i get the error, that the column "pa" doesn't exist. On your file it works fine. I've not changed a thing besides copying it. i even see that column...
let
Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"pa", type number}, {"15", Int64.Type}, {"18", Int64.Type}, {"22", Int64.Type}, {"28", Int64.Type}, {"35", Int64.Type}, {"42", Int64.Type}, {"54", Int64.Type}, {"64", Int64.Type}, {"76,1", Int64.Type}, {"88,9", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"pa"}, "Attribut", "Wert"),
#"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns",{"Wert"},Tabelle2,{"search value"},"Tabelle2",JoinKind.LeftOuter),
#"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "Tabelle2", {"search value"}, {"search value"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded {0}", each [search value] <> null),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"search value"})
in
#"Removed Columns"Hello, i can only guess that the names of your tables are different. In my file and in the code (posted above) the names of the tables are "Tabelle1" und "Tabelle2". Perhaps you can attach your file without sensitive data. This would be very helpful to find the reason for the error message.
OliverScheurich ahh that was the perfect clue. i had a chart in a hidden sheet from testing. now it's working. is it normal, that i have to click "refresh" for the values to change?
Yes, with Power Query we have to click refresh to update the result.