Xlookup syntax

Fida

Your XLOOKUP formula is incorrect. You want to look up the CustomerID from column B of worksheet Orders (that gives an index number, the row number) in the Customers sheet.

In the case of cell G2, the formula is shown below but you are looking in the same sheet/ column (so get the row where 1 appears in the worksheet Orders rather than in Customers). This gives some correct results (by chance) but goes wrong at G2 for example.

=XLOOKUP(B2,B:B,Customers!B:B,,0)

You need to look in column A of the Customers sheet instead, so change it to:

=XLOOKUP(B2,Customers!A:A,Customers!B:B,,0)

Copy that down and your results will match. It will look up the ID in column A of Customers and return what's in the same row but column B of same.

If you want to report errors, XLOOKUP has a fourth parameter (which you omitted) to give a text when an exact match isn't found. Change B2 to this (and copy down) then the cell will read "NOT FOUND" if the CustomerID isn't found.

=XLOOKUP(B2,Customers!A:A,Customers!B:B,"NOT FOUND",0)

If you want such cells even more evident, you could use Conditional Formatting to fill the cell with a colour of change the font colour.

Hope this helps.