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 . The table is not formated as a table.
Reference kolum "January" ( this is a list of months which will be used on monthly basis so it will change each month) shall decide which information shall be applied in the cells of YTD target and Monthly Target.
January = Look-up value
YTD target
Should retrieve data from Columm January - April depending on the given month (ie January in the left and corner) and ge the sum the row ie April should be SUM(KPI 1 January -April) a based on what value is represented in the selection cell.
Monthly target
Should retreieve data from the table based on value in "January"ie January should be "2", February should be "3"
I've tried adifferent types of formulas but I get an error all the time or some other type of default. VLOOKUP is not possible as the positions change due to the month so I can't state that.
Please help!
| January | Months | YTD Target | Monthly Target | |||
| KPI's | January | February | March | April | 2022 | 2022 |
| 1 | 2 | 3 | 4 | 3 | ||
| 2 | 0 | 0 | 0 | 0 | ||
| 3 | 0,5 | 0,5 | 0,5 | 0,5 | ||
| 4 | 25% | 25% | 25% | 25% | ||
| 5 | 4% | 4% | 4% | 4% | ||
| 6 |
I have seen that you have had good solutions OliverScheurich maybe you have a quick fix or a clue how to solve this to this?
Or perphaps you a good HansVogelaar ?
11 Replies
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?
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
