Search code examples
excelexcel-formulaxlookup

How to achieve this requirement using XLOOKUP in Excel?

My requirement is quite straightforward.
I have "Col A" -> Sheet1 that has the entries which are say our reference point
I am matching these entries to ColA in Sheet2
I want the entries in Sheet1 -> ColB to match Sheet2 -> ColB

I have three simple requirements

  1. If the entry matches, display the value in Sheet1 -> ColB
  2. If the entry does not match, display "ERROR" in Sheet1 -> ColB
  3. If the entry matches is the entry is blank, then print blank (else it displays 0) in Sheet1 -> ColB

If I add this it takes care of requirement #1 and #2 =IF(XLOOKUP(A1,Sheet2!A:A,Sheet2!B:B)="","",XLOOKUP(A1,Sheet2!A:A,Sheet2!B:B))

If I add this, it takes of requirement #3 but Excel reports it as an "Inconsistent formula" . How do resolve this ? =IF(XLOOKUP(A1,Sheet2!A:A,Sheet2!B:B,"ERROR")="","",XLOOKUP(A1,Sheet2!A:A,Sheet2!B:B,"ERROR"))

Worth pointing out that I have tried other functions like "ISBLANK" etc but they have all resulted in an error.

Solution

  • Some issues:

    • Your formulas don't match your description. They contain references to two sheets: Sheet2 and Test.
    • The last formula contains a reference to A1 and A230.
    • Your formula logic currently is: If the result of the XLOOKUP is "", then return "", otherwise return the result of the second XLOOKUP, which seems convoluted.

    My guess as to your intent:

    =LET(x,XLOOKUP(A1,Sheet2!A:A,Sheet2!B:B,"ERROR"),IF(x=0,"",x))