Search code examples
arraysgoogle-sheetsgoogle-sheets-formulalookup

Filter blanks from Xlookup search key range

I am using an xlookup function as an arrayformula to sum the prices of ingredients. It functions when the search key I use does not have any blank values, but if there are blank values it errors out. Please see these below screenshots:

Working enter image description here

Not Working enter image description here

What function can I add to allow for blanks under columns D through M?

Solution

  • Just leave the xlookup 4th parameter missing_value either blank OR zero & it should workout.

    XLOOKUP(search_key, lookup_range, result_range, missing_value, match_mode, search_mode)