XLOOKUP() returns "0" instead of "empty cell"

Occasional Visitor

I have to use CORREL() with 2 XLOOKUP but XLOOKUP returns "0" instead of "empty cell" so the result is incorrect. How can i make it return "empty cell" from "empty cell"?

1 Reply


That's an irritating behavior of formulas in general. You can change the XLOOKUP formulas to


=IF(XLOOKUP(...)="", "", XLOOKUP(...))