XLOOKUP and the cleaning formulas you use every week

#Why it matters

"Proficient in VLOOKUP" still appears in Indian job descriptions, because joining two sheets is daily work: a sales export and a price list, an attendance sheet and an employee master. Modern Excel has XLOOKUP, which is safer. Knowing both, and knowing why lookups go wrong, is what the interview checks.

#The concept

A lookup is a join done one cell at a time: for each row, find the matching key in another table and bring back a value.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode])

Why XLOOKUP beats VLOOKUP:

• it looks left as well as right

• it does an exact match by default (VLOOKUP defaults to an approximate match, a classic source of wrong numbers)

• inserting a column does not break it, because you point at the return column instead of counting columns

• it has a built-in "not found" value

In Google Sheets and older Excel, INDEX with MATCH does the same job.

#Worked example: attach product categories

Continue with the sales Table from Pivot tables on half a million sales lines. The dataset uses special stock codes for things that are not products: POST (postage, 1,257 lines), DOT (dotcom postage, 710), M (manual adjustment, 571), D (discount), S (samples) and AMAZONFEE. Create a small sheet codes:

Then in sales, add a column LineType:

=XLOOKUP([@StockCode], codes[StockCode], codes[LineType], "Product")

Every code not in the list becomes "Product". Now your pivot can exclude postage and fees from product revenue with one filter.

Cleaning formulas you will reach for:

=TRIM(CLEAN([@Description]))                  remove extra spaces and hidden characters
=PROPER([@Country])                           consistent capitalisation
=TEXT([@InvoiceDate],"yyyy-mm")               month key for grouping
=IFERROR([@Revenue]/[@Quantity], 0)           avoid #DIV/0!
=VALUE(SUBSTITUTE([@Amount], ",", ""))        "1,234" stored as text to a number
=COUNTIFS(sales[Customer ID],[@[Customer ID]],sales[Invoice],"C*")   cancellations per customer

#Common mistakes

• Approximate-match VLOOKUP. =VLOOKUP(A2, prices, 2) without FALSE at the end can return the wrong row on unsorted data, silently.

• Keys that look equal but are not. "85123A" with a trailing space, or 17850 stored as text in one sheet and as a number in the other. TRIM and VALUE both sides.

• Duplicate keys in the lookup table. A lookup returns only the first match. Check with COUNTIF that each key appears once.

• Wrapping everything in IFERROR. It hides real problems. Use it for known cases only, and count how many rows fell back.

#Interview angle

Expect "What is the difference between VLOOKUP and XLOOKUP?" and "Why might a VLOOKUP return #N/A when the value is clearly there?" The second answer is almost always types or spaces. Saying "I check with =A2=B2 and LEN" shows you have debugged this for real.

#Practice task

1. Build the codes sheet and LineType column. How much revenue is postage?

2. Flag customers who cancelled more than 10 lines, using COUNTIFS.

3. Rebuild one lookup with INDEX and MATCH so it works in any spreadsheet tool.

#Further reading

• Microsoft: XLOOKUP function

• Google Sheets: XLOOKUP