SOLVED

Xlookup - If_not_found not working

%3CLINGO-SUB%20id%3D%22lingo-sub-2391242%22%20slang%3D%22en-US%22%3EXlookup%20-%20If_not_found%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2391242%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20created%20the%20following%20formula%20using%20XLOOKUP%2C%20however%2C%20the%20if_not_found%20does%20not%20seem%20to%20be%20working%20correctly%2C%20continuing%20to%20return%20a%20N%2FA%2C%20instead%20of%20a%20'zero'%20if%20the%20value%20is%20not%20present%20in%20the%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20because%20it's%20an%20array%20formula%3F%20%26nbsp%3B%20How%20could%20I%20amend%20the%20formula%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DXLOOKUP(J6%2C'M%26amp%3BE%20Data'!A%3AA%2CXLOOKUP(BSC!%24C%243%2C'M%26amp%3BE%20Data'!B%243%3AM%243%2C'M%26amp%3BE%20Data'!B%3AM%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20much%20appreciated.%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2391242%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2391321%22%20slang%3D%22en-US%22%3ERe%3A%20Xlookup%20-%20If_not_found%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2391321%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1064725%22%20target%3D%22_blank%22%3E%40lesasp%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%200%20is%20the%20%3CEM%3Eif-not-found%3C%2FEM%3E%20argument%20of%20the%20inner%20XLOOKUP.%20You%20don't%20specify%20%3CEM%3Eif-not-found%3C%2FEM%3E%20for%20the%20outer%20XLOOKUP.%3C%2FP%3E%0A%3CP%3EI%20don't%20understand%20why%20you%20use%20XLOOKUP(BSC!%24C%243%2C'M%26amp%3BE%20Data'!B%243%3AM%243%2C'M%26amp%3BE%20Data'!B%3AM%2C0)%20as%20%3CEM%3Ereturn_array%3C%2FEM%3E%20-%20it%20produces%20a%20single%20value.%20But%20try%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DXLOOKUP(J6%2C'M%26amp%3BE%20Data'!A%3AA%2CXLOOKUP(BSC!%24C%243%2C'M%26amp%3BE%20Data'!B%243%3AM%243%2C'M%26amp%3BE%20Data'!B%3AM%2C0)%2C0)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2391536%22%20slang%3D%22en-US%22%3ERe%3A%20Xlookup%20-%20If_not_found%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2391536%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1064725%22%20target%3D%22_blank%22%3E%40lesasp%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20about%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(SUM(XLOOKUP(J3%2C'M%26amp%3BE%20Data'!A%3AA%2C'M%26amp%3BE%20Data'!B%3AB)%3AXLOOKUP(Calculations!J3%2C'M%26amp%3BE%20Data'!A%3AA%2CXLOOKUP(BSC!%24V%243%2C'M%26amp%3BE%20Data'!B%243%3AM%243%2C'M%26amp%3BE%20Data'!B%3AM)%2C0))%2C0)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@lesasp 

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

@lesasp 

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)

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