Forum Discussion
Return a value from a reference based on multiple kolumns
Is there anyone that could help me identify the formula/s for retrieving a value from a table with multiple kolumns. The two columns which shall get the data are YTD target and Monthy target . T...
In F3:
=SUM(INDEX($B3:$E3;ROW(INDIRECT("1:"&MATCH($A$1;$B$2:$E$2;0)))))
If you are not on Microsoft 365 or Office 2021, confirm the above formula with Ctrl+Shift+Enter.
In G3:
=INDEX($B3:$E3;MATCH($A$1;$B$2:$E$2;0))
Fill down.
The function worked excellent for getting values per month in G3 but unfortunately not in F3, the Sum of months. It seems as if excel does not understand that when I select March I want the sum of values for January+ February + March in the F column. In my case for KPI 1 it would be a total value of 9.
Do you think there might be a solution for this or do I have to calculate the values manually?
Thank you again.
Do you think there might be a solution for this or do I have to calculate the values manually?
Thank you again.
Here is my test workbook. Does it work for you?
- It's finally working!!!
I did some testing here and there and I have the figures I wanted without any manual input. The combination of your two inputs made it work. Thank you so so so much!
What an amazing forum this is. Noone I know could solve this in this rapid speed as you did, along with OliverScheurich.
Thank you again! - Great, I'd like to post my document and see if you can get it working for you. I have literally written everything the same way as you did but still not the total amount in March.
When I open the document before I apply activation I see that you have the 9 in the YTD target, but as soon as I unlock the editing settings number 2 from (probably January) appears instead.
How do I attach a document here? 🙂
=SUM(OFFSET($A$1,ROW(A2),1,1,MATCH($A$1,$B$2:$E$2,0)))
An alternative could be above formula.
- Hi,
This helped a lot, I didn't translate the function correctly.
I did some testing here and there and it is finally working. The combination of your two inputs has made my day. Thank you so much!
SUPER! Vielen dank 🙂 - Thank you so much! I will check it right away.
- Thank you so much for taking the time and looking into this. Are you able to send this sheet in excel? I have looked up the German translations of the function in excel, and then to Swedish as we don't work with the english version (to my frustration) but I'm not sure if I got them all right as I don't get the same result as you.
Thank you!In cell A1 is a dropdown with the values from range J1:J12.
