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's no input in L7?

 

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

 

Using Microsoft 365 Apps for enterprise

 

Best, 

 

DH

Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jan 04, 2023

    Hi danhill365 

     

    What about?

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

     

     

     

    • 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!

Resources