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.
=INDEX(A2:A4,SUM(MMULT(N(TRANSPOSE(B2:E4=H1)),ROW(1:3))))
=INDEX(B1:E1,SUM(MMULT(N(B2:E4=H1),ROW(1:4))))
These formulas work in my sample file if there are unique values in range B2:E4. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021. The search value is in cell H1 in the sample file.
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.