GMonth, LAMBDA(x, XLOOKUP(x, tMonths,months)), SPLIT,LAMBDA(x,LET(Ī,TEXTBEFORE(x,tMonths),b,SUBSTITUTE(x,a,""),HSTACK(a,gMonth(b)))), Lks, DROP(fcts,2), aps, TAKE(DROP(fcts,1),1), idx, SEQUENCE(COLUMNS(months)), Months, EDATE(1,SEQUENCE(1,12,0)), tMonths, UPPER(TEXT(months, loc)), more than one target code, different AP codes: =LET(setA, A2:B13, setB, C2:D13, fcts, F2:R4, loc, "mmmm", Here is the formula that covers a very general case, i.e. The AP codes don't have to be the same (even in the sample data are all the same).Īll previous assumption are based on the sample input data from the question.For any keys there is data for the 12 months.The information for a given key doesn't need to come one after another.The rows don't need to match, only the order within the same key for the dates should be the same for both tables.The input data is organized chronologically in ascending order, but it works for descending too. The input data in both input tables are organized in a similar manner (otherwise we would need and additional lookup), i.e, the costs and days for a given key are organized following the same logic.The following is an array formula, that spills the entire result and considers more than one target key, even in the example there is only one value. Please, note that I want a constant value, so I am not expecting to use ctrl+shift+enter.Īssuming no Excel version constraints as per the tags listed in the question. In this formula I have the "1" of the fact_table on G27 cell (hence offset(G27 25-row() 0) gives me "AP01100" while offset(G27 26-row() 0)) gives me "GENNAIO")
the wrong way) but I really hope there is something smarter: I started writing the following formula (only for the first person to recruit on january and it's missing a vlookup for gg_Target, anyway I think it's clear the way I am thinking about it. The meaning is: I want to recruit 1 person from january to december and 4 people from february to december (that's why the "4" from fact table ignores january information) (0 * mar_cost * mar_days + 0 * apr_cost * apr_days +.
(4 * feb_cost * feb_days + 4 * mar_cost * mar_days +. (1 * jan_cost * jan_days + 1 * feb_cost * feb_days +. I need to do this formula for each row of the fact table (to be added next to "DICEMBRE"):
0 kommentar(er)
