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