Excel GuidesSpreadsheet Tips

XLOOKUP Says Zero. Was the Cost Actually Found?

A product sells for $120. Your margin report shows no cost against it. That could mean a free sample, a legitimate adjustment or a missing match. Excel can display the same friendly zero for very different reasons.

The trouble often starts with XLOOKUP’s optional “if not found” argument. Set it to 0 and an unmatched product code returns zero. The formula has done exactly what you asked. The report has quietly promoted “we could not find it” to “it cost nothing.”

Give missing records their own label

Suppose sales product codes are in A2:A500 and the cost table has codes in H2:H500, with unit costs in I2:I500. For a product in A2, use an explicit exception label:

=XLOOKUP(A2,$H$2:$H$500,$I$2:$I$500,"CHECK: NO MATCH",0)

The final 0 requests an exact match. The text in the fourth argument identifies a missing code. This formula assumes your version of Excel supports XLOOKUP; Microsoft notes that Excel 2016 and 2019 do not.

Filter the result column for that label and investigate those records before calculating margin. Count the unmatched products and their sales value separately. An overall margin calculated only from matched records should say that it covers a subset of sales.

A successful match also needs inspection. A matched row can contain a blank or an explicit zero cost. Check the source cost cell rather than assuming every numeric result represents a complete cost record. Confirm currency, unit of measure and the period the cost applies to.

XLOOKUP normally returns the first match it finds. If the cost table contains several periods for one product, decide which record applies before using it as the lookup source. Changing the search direction does not establish a business rule.

For a workbook review, upload the sales and cost tables to Jolene.pro and ask: “Separate unmatched product codes, matched blank costs and matched zero costs. Show the sales value affected by each group and the source rows. Do not treat missing costs as zero.”

Jolene.pro provides source references and calculation steps to inspect. Resolve the exceptions with the person who owns the cost table before sharing a margin conclusion. The useful outcome is a short, explainable review list. A disappearing error is a much smaller achievement.

Reference: Microsoft’s XLOOKUP documentation.

Leave a comment