VLOOKUP and XLOOKUP: A Step-by-Step Explanation

There are two tables: one holds orders, the other customer phone numbers. Copying the phone next to each order by hand — half an hour for a hundred rows, all day for a thousand. VLOOKUP does that job with one formula. In Excel, the first formula that accelerates a career is usually this one.

The short answer to "what is VLOOKUP": a function that searches for a value in one table and brings back another column's data from that row. In this article you will see the syntax, a working example, the classic mistakes and when the more modern XLOOKUP is superior.

How VLOOKUP works: the syntax

The function has four parts:

=VLOOKUP(lookup_value; table_range; column_number; [exact_match])

The memorable rule: VLOOKUP always looks left to right. The value you search must sit in the table range's first column; the result comes from the columns to its right. If you need to look left — that is already XLOOKUP's job.

A step-by-step example: adding phone numbers to orders

The situation: on the "Orders" sheet, column A holds the customer code. On the "Customers" sheet, column A is the code, column B the name, column C the phone.

• Go to the first cell of an empty column on the Orders sheet.
• Type: =VLOOKUP(A2; Customers!A:C; 3; FALSE)
• Enter; the code is found and the phone from the third column arrives.
• Drag the formula down; every row searches with its own code.

When a code is not found, the #N/A error will appear. That must be managed, not hidden: =IFERROR(VLOOKUP(...); "Not found"): a clear message instead of an error. But careful: a flood of "Not found" results usually points to a data problem, not a formula problem.

The 4 most common VLOOKUP mistakes

• Not locking the range: when you drag the formula down, the table range slides. Fix: use the $A$1:$C$100 format or whole columns (A:C).
• Forgetting exact match: when the fourth argument is left blank, Excel searches "approximately" and quietly returns a wrong result. Always write FALSE.
• Invisible differences: "A101 " (with a trailing space) and "A101" are different values; so are "101" stored as a number and "101" stored as text. The TRIM function and a format check are the first defence.
• The column number breaking: when you add a new column to the table, "3" now lands on another column. That fragility is one of the main reasons to move to XLOOKUP.

XLOOKUP: what changes, and when to switch?

XLOOKUP, available in Microsoft 365, does the same job more cleanly:

=XLOOKUP(lookup_value; lookup_range; return_range; [if_not_found])

Practical advice: prefer XLOOKUP in new files. Knowing VLOOKUP is still necessary; work with old files, other people's spreadsheets and old Excel versions does not end. If you are a Google Sheets user, both functions work there with the same logic.

One step back: the data before the formula

An observation from experience: in most "VLOOKUP doesn't work" complaints, the culprit is not the formula but the table — duplicated codes, mixed formats, text with stray spaces. Five minutes of preparation (check duplicates, fix formats, run TRIM) solves half the formula problems in advance. A simple formula on tidy data beats a complex formula on messy data.

Sources and further reading

Where to verify the source

Function syntax can change with the version; check the exact arguments in the official documentation.

• Microsoft Support: VLOOKUP function: the official syntax and examples
• Microsoft Support: XLOOKUP function: the full description of the modern alternative
• Google Docs Editors Help: VLOOKUP: for the Google Sheets version

Continuing the topic

Lookup functions are one part of spreadsheet work; for the remaining skills:

• Learning Excel: a practical guide from zero
• The most important Excel formulas
• Pivot Tables: turn data into reports in seconds
• Google Sheets: the differences and advantages vs Excel
• Other articles on this topic

Run VLOOKUP once on your own real table; five minutes of practice teaches more than a hundred readings. The first column you would copy by hand in tomorrow's report is exactly that practice's material.