I have an array with several columns that are spill ranges. I need to use each column to do a multi-conditional xlookup (or similar formula/result) that gives a spill range of the results. I used a sumifs to get results but when a cell is empty I would like it to remain empty, where the sumifs results gives 0% when a cell is empty.
The real application of this will be put into a LET formula and each of the spills in the xlookup is a choosecols output.
=XLOOKUP(1,($C$3#=$C$12:$C$15)*($D$3#=D12:D15),$E$12:$E$15,"",0)
=SUMIFS($E$12:$E$15,$C$12:$C$15,C3#,$D$12:$D$15,D3#)
In the image the borders denote a spill array. Expected results are manually entered.
>Solution :
You could try using the following formula:
=MAP(C3#,D3#,LAMBDA(α,δ,
XLOOKUP(1,(α=C12:C15)*(δ=D12:D15),
IF(E12:E15="","",E12:E15),"")))
Or,
=LET(a, SUMIFS(E12:E15,C12:C15,C3#,D12:D15,D3#), IF(a=0,"",a))
Or,
=LET(
a, FILTER(E12:E15,COUNTIFS(C3#,C12:C15,D3#,D12:D15)),
IF(a=0,"",a))
Or,
=XLOOKUP(C3#&"|"&D3#,C12:C15&"|"&D12:D15,IF(E12:E15=0,"",E12:E15))