Jan 04 2023 11:29 AM
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
Jan 04 2023 12:02 PM - edited Jan 04 2023 12:03 PM
Hi @danhill365
What about?
=IF(ISBLANK(L7), ""
XLOOKUP(L7,'Emp.12.27 In'!D2:D14000,'Emp.12.27 In'!A2:A14000,"")
)
Mar 06 2024 09:49 AM
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!
Mar 06 2024 12:51 PM
Try
=LET(r, XLOOKUP(L7,'Emp.12.27 In'!D2:D14000,'Emp.12.27 In'!A2:A14000,""), IF(r="", "", r))
Mar 06 2024 01:24 PM
Thanks @Hans Vogelaar, 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.
Mar 06 2024 02:47 PM
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.