Re: Xlookup - If_not_found not working

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