Forum Discussion
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)
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)
HansVogelaar =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?
Try this version:
=XLOOKUP(C8, AMAZONDATA!A:A, AMAZONDATA!B:B, XLOOKUP(C8, AMAZONDATA!F:F, AMAZONDATA!B:B, ""))
HansVogelaar 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]
- 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))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)
