Forum Discussion
Solved
Xlookup - If_not_found not working
Hi, I have created the following formula using XLOOKUP, however, the if_not_found does not seem to be working correctly, continuing to return a N/A, instead of a 'zero' if the value is not present in...
How about
=IFERROR(SUM(XLOOKUP(J3,'M&E Data'!A:A,'M&E Data'!B:B):XLOOKUP(Calculations!J3,'M&E Data'!A:A,XLOOKUP(BSC!$V$3,'M&E Data'!B$3:M$3,'M&E Data'!B:M),0)),0)
The 0 is the if-not-found argument of the inner XLOOKUP. You don't specify if-not-found for the outer XLOOKUP.
I don't understand why you use XLOOKUP(BSC!$C$3,'M&E Data'!B$3:M$3,'M&E Data'!B:M,0) as return_array - it produces a single value. But try
=XLOOKUP(J6,'M&E Data'!A:A,XLOOKUP(BSC!$C$3,'M&E Data'!B$3:M$3,'M&E Data'!B:M,0),0)
Thank you Han's, that has worked perfectly. I have the same problem with this formula also, but the fix you advise does not seem to work. Any ideas?
=SUM(XLOOKUP(J3,'M&E Data'!A:A,'M&E Data'!B:B):XLOOKUP(Calculations!J3,'M&E Data'!A:A,XLOOKUP(BSC!$V$3,'M&E Data'!B$3:M$3,'M&E Data'!B:M),0))
=SUM(XLOOKUP(J3,'M&E Data'!A:A,'M&E Data'!B:B):XLOOKUP(Calculations!J3,'M&E Data'!A:A,XLOOKUP(BSC!$V$3,'M&E Data'!B$3:M$3,'M&E Data'!B:M),0))