Forum Discussion
danhill365
Jan 04, 2023Copper Contributor
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...
HansVogelaar
Mar 06, 2024MVP
Try
=LET(r, XLOOKUP(L7,'Emp.12.27 In'!D2:D14000,'Emp.12.27 In'!A2:A14000,""), IF(r="", "", r))
BJohnsonColo303
Mar 06, 2024Copper Contributor
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.
- HansVogelaarMar 06, 2024MVP
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.