May 27 2021 05:30 AM
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.
May 27 2021 05:54 AM
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)
May 27 2021 06:04 AM
May 27 2021 06:39 AM
SolutionHow 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)
May 27 2021 07:08 AM
Aug 21 2023 12:51 PM
@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?
Aug 21 2023 12:54 PM
Try this version:
=XLOOKUP(C8, AMAZONDATA!A:A, AMAZONDATA!B:B, XLOOKUP(C8, AMAZONDATA!F:F, AMAZONDATA!B:B, ""))
Aug 21 2023 01:06 PM - edited Aug 21 2023 01:09 PM
@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]
Aug 21 2023 01:36 PM
Aug 21 2023 02:22 PM
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,""))
May 27 2021 06:39 AM
SolutionHow 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)