Xlookup - If_not_found not working

[lesasp]

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.  

[#2]

@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)

[#3]

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))

[#4 · Accepted 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)

[#5]

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

[#6]

@Hans Vogelaar =XLOOKUP(TRUE,ISNUMBER(SEARCH(AMAZONDATA!A:A,C8)),AMAZONDATA!B:B,XLOOKUP(TRUE,ISNUMBER(SEARCH(AMAZONDATA!F:F,C8)),AMAZONDATA!B:B,""))

For the [if_not_found] section, I want the formula to reattempt the search in a different column (F) and return that data.  Is this incorrect?

[#7]

@HLowell 

Try this version:

=XLOOKUP(C8, AMAZONDATA!A:A, AMAZONDATA!B:B, XLOOKUP(C8, AMAZONDATA!F:F, AMAZONDATA!B:B, ""))

[#8]

@Hans Vogelaar Yeah, I had to keep it "dirty" as the values in the AMAZONDATA! sheet contain less characters than the Values being referenced on the main sheet.  Imported data has the apostrophe to indicate text follows, so it's not an exact match reference.  That's how I landed on the (TRUE,ISNUMBER(SEARCH( )) for [lookup_value][lookup_array]

[#9]

@HLowell 

And? Does your formula work?

[#10]

Appologies, no. The [if_not_found] values show up as 0 instead of searching the second Column (F). I am having difficulties figuring out how to return a different value when Column A fails the cross-reference.

[#11]

@HLowell 

The problem is ISNUMBER(SEARCH(C8,A:A)) will be true for all empty cells in column A.

You can use the following. It's better to use finite ranges here instead of entire columns.

=XLOOKUP(1,ISNUMBER(SEARCH(AMAZONDATA!A1:A1000,C8))*(AMAZONDATA!A1:A1000<>""),AMAZONDATA!B1:B1000,XLOOKUP(1,ISNUMBER(SEARCH(AMAZONDATA!F1:F1000,C8))*(AMAZONDATA!F1:F1000<>""),AMAZONDATA!B1:B1000,""))