Excel FILTER formula using spill range in criteria

userTKvE 0
Viewed 21

I'm having trouble getting the FILTER formula to work using a spillover range. Here's is a sample scenario:

I have a spillover range in E2 that gives me a list of numbers I would like to lookup. The lookup table is A2:B6. If I use the following formula in F2 and drag it down to F4, everything works perfectly:

   =LET(ProductFound, UNIQUE(FILTER($B$2:$B$6, $A$2:$A$6=E2, "Nothing found")),
    IFS(ROWS(ProductFound)>1, "There are two products with the same SKU #",
        ProductFound="Nothing found", "No Such Product with SKU #",
        TRUE, ProductFound))

However, I would like F2 to be a spillover formula that automatically matches the size of E2 so I do not have to perform any manual dragdown but I can't figure a way. When I change the formula to use the spillover range operator I only get #N/A in F2:

   =LET(ProductFound, UNIQUE(FILTER($B$2:$B$6, $A$2:$A$6=E2#, "Nothing found")),
    IFS(ROWS(ProductFound)>1, "There are two products with the same SKU #",
        ProductFound="Nothing found", "No Such Product with SKU #",
        TRUE, ProductFound))

Can anyone help with a solution? Thanks.

filter excel
1 Answers

Use BYROW:

=BYROW(E2#,LAMBDA(a,LET(ProductFound, UNIQUE(FILTER($B$2:$B$6, $A$2:$A$6=a, "Nothing found")),
IFS(ROWS(ProductFound)>1, "There are two products with the same SKU #",
    ProductFound="Nothing found", "No Such Product with SKU #",
    TRUE, ProductFound))))

enter image description here

Scott Craner0
Related