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.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!
=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 try the formula right away. This forum is really fantastic! I never thought I'd get a reply so quick.
I'll get back to on the progress.
Once again, big thank you!
/Barbara
