Forum Discussion
Help with formula
Hello, I literally can not come up with the solution, and I have tried sum product or sum ifs, match 2 criteria`s to find the data belonging to it. Which formula should I be using? index&match ?...
Steven35-31 Another option is to unpivot the data using a set criteria. For example:
=LET(
UNPVT, LAMBDA(n, TOCOL(IFS(F3:T1000<0, CHOOSE(n, D3:D1000, F2:T2, F3:T1000)), 2)),
HSTACK(UNPVT(1), UNPVT(2), UNPVT(3))
)
Sample Results
Based on your sample data, I assumed all "(booking)" amounts are positive and all invoiced amounts are negative, so I set the criteria to return only records with an amount less than zero. If that's not the case, another variation could be:
=LET(
UNPVT, LAMBDA(n, TOCOL(IFS(F3:T1000<>"", CHOOSE(n, D3:D1000, F2:T2, F3:T1000)), 2)),
FILTER(HSTACK(UNPVT(1), UNPVT(2), UNPVT(3)), UNPVT(1)<>"(booking)")
)
See attached...