SOLVED

# Xlookup - If_not_found not working

Copper Contributor

# 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.

10 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

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

# Re: Xlookup - If_not_found not working

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

# Re: Xlookup - If_not_found not working

Try this version:

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

# Re: Xlookup - If_not_found not working

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

# Re: Xlookup - If_not_found not working

And? Does your formula work?

# Re: Xlookup - If_not_found not working

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.

# Re: Xlookup - If_not_found not working

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

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution