Almost every data analyst job involves matching data from two tables: a list of order IDs in one sheet and customer names in another. In Excel, the classic tool for this is VLOOKUP. Newer versions of Excel also have XLOOKUP. Both work, but they behave differently, and knowing why saves hours of debugging.
How VLOOKUP works
VLOOKUP looks for a value in the first column of a range and returns a value from a column to the right, chosen by number. For example, =VLOOKUP(A2, Customers!A:D, 3, FALSE) finds the ID in A2 and returns the third column of the customer table.
Three common problems:
- It cannot look left. The lookup column must be the first column of the range.
- The column number breaks easily. If someone inserts a column in the source table, “3” now points to the wrong data, and Excel gives no warning.
- The default match is approximate. If you forget the last argument (FALSE), VLOOKUP may return a wrong value instead of an error.
How XLOOKUP works
XLOOKUP takes a lookup column and a return column separately: =XLOOKUP(A2, Customers!A:A, Customers!C:C, "Not found"). This fixes all three problems:
- The return column can be to the left or right.
- Inserting columns does not break it, because you point at columns, not count them.
- Exact match is the default, and the fourth argument lets you show a clear message when nothing is found.
Which should you learn first?
Learn both, but use XLOOKUP for new work when your Excel version has it (Microsoft 365 and Excel 2021 onwards). You still need to read VLOOKUP, because many existing company files use it, and interviewers often ask about it.
A quick practice task
- Create an Orders sheet with Order ID and Customer ID.
- Create a Customers sheet with Customer ID, Name and City.
- Bring the customer name into Orders with VLOOKUP, then with XLOOKUP.
- Insert a new column in the Customers sheet and see which formula still returns the right name.
This small exercise shows exactly why analysts moved to XLOOKUP. Next, read Excel formulas every data analyst should know and data cleaning in Excel.
Excel, SQL and Power BI are the core of our 5-month Data Analytics course in Bangalore.
Book a free demo class at our BTM Layout centre, and ask a counsellor for batch details.
