Forum Discussion
Find values depending on searched value in chart
Hey, so i'm relatively new to excel.
i have a excel chart looking like this:
| 10mm | 20mm | 30mm | 40mm |
| 0.1Pa | 5 kW | 6 kW | 7 kW | 8 kW |
| 0.2Pa | 9 kW | 10 kW | 11 kW | 12 kW |
| 0.3Pa | 13 kW | 14 kW | 15 kW | 16 kW |
now i want to search a kW value out of this chart(10kW for example) and excel should show me the corresponding mm and Pa. (20mm/0,2Pa). The SI units are included via textformat.
I've tried to do my best with x/vlookup(=XVERWEIS(M17;J2:J20;A2:A20)), but excel only accepts one row/line to search.
Is there a good way or do i need multiple vlookups in an if/else configuration to search every line/row?
=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.
9 Replies
=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.