 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 the data.

Is this because it's an array formula?   How could I amend the formula please?

=XLOOKUP(J6,'M&E Data'!A:A,XLOOKUP(BSC!\$C\$3,'M&E Data'!B\$3:M\$3,'M&E Data'!B:M,0))

Any help is much appreciated.

4 Replies

# Re: Xlookup - If_not_found not working

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)

# Re: Xlookup - If_not_found not working

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))
best response confirmed by allyreckerman (Microsoft)
Solution

# Re: Xlookup - If_not_found not working

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)

# Re: Xlookup - If_not_found not working

Worded perfectly. Thank you so much for your help :0)