Forum Discussion

danhill365's avatar
danhill365
Copper Contributor
Jan 04, 2023

Xlookup Formula return 0 but working

Hi!   Me again. I have a working xlookup. L7 is text based (full name) input and works just fine. When I leave  L7 blank then I get 0. How can I update the formula to return a blank cell when there...
Formulas and Functions
BJohnsonColo303's avatar
BJohnsonColo303
Copper Contributor
Mar 06, 2024

Lorenzo 

 

Would like to know the answer to this...I've checked and rechecked the formula.  I'm not able to find out why in specific cells it returns "0" when in other cells with the exact same formula I can use the "If_not_found" function.  It looks like a bug to me.  I've only found a variety of workarounds on the internet, it would be nice to understand why it is occurring.  Thanks!

HansVogelaar's avatar
HansVogelaar
MVP
Mar 06, 2024

BJohnsonColo303 

Try

 

=LET(r, XLOOKUP(L7,'Emp.12.27 In'!D2:D14000,'Emp.12.27 In'!A2:A14000,""), IF(r="", "", r))

  • FennekMeta's avatar
    FennekMeta
    Copper Contributor
    Aug 09, 2024

    HansVogelaar Dank je wel Hans! Thank you Hans! Thanks to your suggestion, I have learned a very handy formula e.g. function.

  • BJohnsonColo303's avatar
    BJohnsonColo303
    Copper Contributor
    Mar 06, 2024

    Thanks HansVogelaar, I will try that.  I saw other workarounds online.  My main curiosity is why does it sometime create a "0" and sometimes if follows the desired value for "If_not_found"?

     

    thanks.

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Mar 06, 2024

      BJohnsonColo303 

      VLOOKUP and XLOOKUP will return 0 if the lookup value is found in the lookup column, but the corresponding cell in the return column is blank.

Resources