Search code examples
excelexcel-formula

Multi-Conditional Xlookup with spill array inputs

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.

image

Solution

  • You could try using the following formula:

    enter image description here

    =MAP(C3#,D3#,LAMBDA(α,δ, 
 XLOOKUP(1,(α=C12:C15)*(δ=D12:D15),
 IF(E12:E15="","",E12:E15),"")))

    Or,

    =LET(a, E12:E15, IF(a="","",SUMIFS(a,C3#,C12:C15,D3#,D12:D15)))

    Or,

    =LET(
     a, FILTER(E12:E15,COUNTIFS(C3#,C12:C15,D3#,D12:D15)),
     IF(a="","",a))

    Or,

    =XLOOKUP(C3#&"|"&D3#,C12:C15&"|"&D12:D15,IF(E12:E15="","",E12:E15))