Forum Discussion
Solved
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...
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)
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.
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,""))