Lookups: VLOOKUP and XLOOKUP Explained
This lesson is your first real introduction to lookups — the single most requested Excel skill in job postings. Instead of diving straight into syntax, we'll build the mental model first: what a lookup actually does, why spreadsheets need them, and how two tables become one connected system. Once the concept clicks, VLOOKUP and XLOOKUP are just two ways of expressing the same idea.
Why this skill matters professionally
Almost every office job that touches spreadsheets eventually needs to connect two lists: an order number to a customer name, an employee ID to a department, a SKU to a price. Employers list "VLOOKUP" as a screening keyword on job postings for coordinator, analyst, and admin roles because it signals you can work with real business data instead of retyping it by hand. Someone who understands lookups can merge a vendor's price file into their own report in thirty seconds; someone who doesn't spends an afternoon copy-pasting and introduces errors along the way.
Learning objectives
- Understand what a lookup does.
- Write a working VLOOKUP.
- Use XLOOKUP as a modern replacement.
Background concepts
What a lookup actually does
A lookup answers one question: "Given this known value, what's the matching value in another table?" You already do this by eye when you scan a printed price list for a product code and read across the row to find its price. A lookup formula automates that scan.
Two tables, one key
Lookups only work when both tables share a common column — a unique identifier called the key. It might be a product code, employee ID, invoice number, or email address. Excel searches for that key in one table and pulls back a value from the same row in another column.
- The key must exist, spelled identically, in both tables.
- The key should ideally be unique — duplicates make lookups return only the first match.
- Extra spaces or inconsistent formatting (text vs. number) are the #1 cause of lookup failures.
Why two functions exist
VLOOKUP has existed since the 1990s and is supported in every version of Excel, Google Sheets, and most compatible tools. XLOOKUP is newer (2019+) and fixes several of VLOOKUP's structural limitations. You'll see both in the wild for years to come, so this lesson introduces both conceptually before the next lesson goes deep on syntax.
The building blocks of any lookup
Regardless of which function you use, every lookup formula needs three pieces of information.
- What you're looking for (the lookup value)
- Where to search for it (the lookup range)
- What to bring back (the return range or column)
Step-by-step
1. Step 1: Identify your key column
Look at both tables and find the column they have in common. In a table of orders and a table of customers, that's usually a Customer ID or email address, not the customer's name (names can have typos or duplicates).
2. Step 2: Decide what you want returned
Ask: 'once Excel finds the matching row, what value do I actually want?' It might be a price, a name, a status, or a date. This becomes your return column.
3. Step 3: A basic VLOOKUP
VLOOKUP searches down the leftmost column of a table and returns a value from a column you specify by number, counting left to right.
=VLOOKUP(A2, ProductTable, 3, FALSE) | | | | | | | +-- FALSE = exact match | | +-- return the 3rd column of ProductTable | +-- the table to search +-- the value to find (must be in ProductTable's 1st column)
4. Step 4: A basic XLOOKUP
XLOOKUP is more literal — you point at the exact column to search and the exact column to return, with no counting required.
=XLOOKUP(A2, ProductTable[Code], ProductTable[Price]) | | | | | +-- return values from this column | +-- search for A2 in this column +-- the value to find
5. Step 5: Test with a known value
Before trusting a lookup across a whole column, test it on one row where you already know the answer. If the price for SKU-100 should be $24.99, confirm the formula returns exactly that before copying it down.
6. Step 6: Copy the formula down
Lock the table reference with $ signs (or use a Table/named range, which locks automatically) so it doesn't shift as you copy the formula to other rows. Then fill down the column.
7. Step 7: Handle 'not found' gracefully
If a code doesn't exist in the source table, VLOOKUP returns #N/A and XLOOKUP does too — unless you tell it otherwise. Wrap the formula or use XLOOKUP's built-in fourth argument to show something readable like 'Check code' instead of an error.
8. Step 8: Know when NOT to use a lookup
If you just need to add two columns together or filter a list, a lookup is the wrong tool. Lookups are specifically for pulling data from a *different* table based on a shared key.
Real-world workplace examples
Price lookup on an order form
A sales rep types a product code into an order form; a lookup automatically fills in the description and price from the master price list, eliminating typos and mismatched pricing.
HR merging payroll and directory data
An HR coordinator pulls department and manager name into a payroll export by matching on Employee ID, turning a flat payroll file into a report managers can actually read.
Reconciling bank feeds
A bookkeeper looks up each bank transaction ID against the accounting system's transaction list to flag which ones are missing or duplicated.
Building a dashboard from raw exports
An analyst exports data from a CRM and a billing system separately, then uses a lookup to combine them into one row per client for a status dashboard.
Practical scenarios
The 'name doesn't match' mystery
A coordinator builds a lookup matching customer names between two systems and gets #N/A errors on 15 out of 200 rows. Investigating, she finds the CRM stores 'Robert Chen' while billing stores 'Bob Chen' — the names aren't actually identical. This is exactly why keys should be IDs, not names: names are inconsistent, IDs are stable. The fix is to request a shared ID field from IT rather than patching name mismatches by hand.
Choosing between two valid tools
A new analyst inherits a workbook full of VLOOKUP formulas and wonders whether to convert everything to XLOOKUP. The pragmatic answer: don't rewrite what already works, but use XLOOKUP going forward for new formulas, since it's less fragile if columns get reordered later. This reflects how most real workplaces evolve — old and new coexist.
Common mistakes beginners make
Forgetting exact match
Leaving off FALSE (or using TRUE) makes VLOOKUP assume the data is sorted and do an approximate match, silently returning the wrong row. Always specify FALSE for exact match unless you deliberately need range lookups like tax brackets.
Lookup value not in the first column
VLOOKUP can only search the leftmost column of its table range. If your key is in column D but you're using columns A:D, it fails — swap column order or switch to XLOOKUP, which has no such restriction.
Mixing text and numbers
A code stored as text ('00123') won't match a lookup value stored as a number (123). Format both consistently, or convert with VALUE()/TEXT().
Not locking the table reference
Copying a lookup formula down without locking the range with $ signs causes the table reference to shift, eventually pointing at blank cells and returning errors.
Trusting the first result blindly
If the key column has duplicates, a lookup always returns the first match, silently hiding the rest. Always check whether your key is genuinely unique.
Best practices
- Use a stable, unique ID as the lookup key whenever one is available.
- Always specify exact match — FALSE in VLOOKUP, default in XLOOKUP.
- Test the formula on a row with a known answer before copying it everywhere.
- Convert your source table into an Excel Table so references stay locked automatically.
- Wrap lookups that might fail in IFERROR or use built-in not-found handling.
- Keep the source table on its own sheet or clearly labeled area so it's easy to audit.
- Prefer XLOOKUP for anything new; know VLOOKUP because it's everywhere in existing files.
Professional tips
- Name your source range (Formulas → Define Name) so formulas read like =VLOOKUP(A2, PriceList, 3, FALSE) instead of cryptic cell ranges.
- Select the lookup formula and press F9 in the formula bar to see intermediate results while debugging — press Esc after to avoid overwriting the formula.
- If a lookup keeps returning #N/A, wrap the lookup value in TRIM() to strip invisible leading/trailing spaces — the most common invisible culprit.
- Use Ctrl+` (grave accent) to toggle Show Formulas view and audit a sheet full of lookups at once.
- For a quick sanity check, use COUNTIF to confirm a key exists exactly once in the source table before you build the lookup.
Practice exercises
Build your first lookup
Create two small tables: one with Product Code/Name/Price, one blank order form with just Product Code. Write a VLOOKUP that pulls Name and Price automatically when you type a code.
Break it on purpose
Type a product code that doesn't exist in your table and observe the #N/A. Then wrap your formula in IFERROR to display 'Code not found' instead.
Convert to XLOOKUP
Rewrite your VLOOKUP formulas from the first exercise as XLOOKUP formulas and compare how much easier it is to point at the exact columns.
Spot the duplicate key
Add a duplicate product code to your source table with a different price, then observe which row your lookup returns. Use COUNTIF to detect the duplicate.
Review questions
What three pieces of information does every lookup formula need?
The value to find, the range to search in, and the range or column to return a value from.
Why should you use an ID instead of a name as your lookup key?
Names can be spelled inconsistently or duplicated between systems, while IDs are unique and stable, making matches reliable.
What happens if you leave VLOOKUP's fourth argument as TRUE or blank?
It performs an approximate match assuming sorted data, which can silently return the wrong row instead of erroring.
Why is XLOOKUP considered more flexible than VLOOKUP?
It can search and return from any column in any direction, without needing the lookup column to be the leftmost one.
What's the most common invisible cause of a failed lookup?
Extra spaces or mismatched text/number formatting in the key values.
Key takeaways
- A lookup connects two tables through a shared key column.
- Always identify the lookup value, search range, and return range before writing the formula.
- VLOOKUP requires the key to be the leftmost column; XLOOKUP does not.
- Always use exact match to avoid silent wrong-row errors.
- Test on a known value before trusting a formula across an entire column.
- Duplicate keys mean you'll only ever get the first match — verify uniqueness.
- XLOOKUP is the modern standard, but VLOOKUP remains everywhere in existing files.
Frequently asked questions
Do I need to learn VLOOKUP if XLOOKUP is better?
Yes — countless existing workbooks and job requirements still use VLOOKUP, so you need to read and maintain it even if you write new formulas with XLOOKUP.
Can a lookup search across different sheets?
Yes, the table argument can reference a range on any sheet in the workbook, e.g. =VLOOKUP(A2, Prices!A:C, 3, FALSE).
Can a lookup search across different workbooks?
Yes, but it's fragile — if the source file moves or is renamed, the link breaks. Prefer consolidating into one workbook when possible.
What does #N/A actually mean?
It means Excel searched the specified range and found no exact match for your lookup value — it's not a formula error, it's a 'not found' result.
Is XLOOKUP available in older Excel versions?
No, it requires Microsoft 365 or Excel 2021+. Files built with XLOOKUP will show errors in older Excel versions, so check your audience first.
What's the difference between a lookup and a filter?
A lookup returns one matching value per row from a second table; a filter narrows down which rows of a single table are shown or returned.
Related lessons
12 min • Beginner
15 min • Easy
20 min • Advanced
15 min • Beginner