xlookup returning "0" in some cells, but works in about 95% of the table

[AustSportingPix]

This is a weird one, I've been using xlookup for the longest time, but I'm getting a weird thing happen now.

In a few of my tables when I use xlookup it'll work, but on a few rows it'll return 0.

The only way around it at the moment is "=if(xlookup=0,"",xlookup" and when you've got 50,000 rows its getting very slow and the files are getting huge.

ANy idea whats happening?

 

[Scott Huish]

What is your formula?
Can you post the sample data (especially of the one that is failing)?

Is the cell in the return_array blank by chance?

 

[AustSportingPix]

XLOOKUP([@Horse],Dec22_[Horse],Dec22_[1],"")

I'm now being forced to use: =IF(XLOOKUP([@Horse],Dec22_[Horse],Dec22_[1],"")=0,"",(XLOOKUP([@Horse],Dec22_[Horse],Dec22_[1],""))) to get rid of the "0" in some cells

 

[Alex Blakenburg]

As Scott mentioned a return value of zero typically means that the column you are returning contains either a blank or a “”.
Without seeing a sample of your data we don't know what the data type is, if you are only returning text you could have simply added & ""
eg

=XLOOKUP([@Horse],Dec22_[Horse],Dec22_[1],"") & ""

Assuming you are returning a number and since you have 365 the below would be more efficient.

=Let(returnvalue, XLOOKUP([@Horse],Dec22_[Horse],Dec22_[1],""),
        If(returnvalue = "", "", returnvalue)