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.
OliverScheurichthank you!
your sheet works fine here, but now i'm trying to largescale it and can't seem to get it working.
Sadly my excel translates everything to german commands, but they should be the same.
what am i doing wrong? putting it as an array funtion doesn't work either.
i'm getting a #VALUE! error.
=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 thanks a lot, works perfectly now.
Could you give me a hint for my problem with the recurring numbers?
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...