VLOOKUP in Excel — Complete Guide: Syntax, Examples & Error Fixes (2026)

VLOOKUP is one of the most searched Excel functions of all time — and for good reason. Whether you are pulling prices from a product table, matching customer names to IDs, or building commission tiers, VLOOKUP is the workhorse that makes it happen. This guide covers everything: the full syntax, exact vs approximate match, IFERROR handling, cross-sheet lookups, returning multiple columns, wildcard matching, all six common errors with fixes, and a full comparison against INDEX-MATCH and XLOOKUP so you know exactly when to use each one.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

What is VLOOKUP?

VLOOKUP stands for Vertical Lookup. The function searches for a value in the first column of a specified range and returns a value from another column in the same row. The "V" distinguishes it from HLOOKUP, which searches horizontally across a row — but in practice almost all lookup data is structured vertically (one record per row), making VLOOKUP the far more widely used of the two.

Here is the core problem it solves: you have a spreadsheet of 2,000 orders. Column A contains a Product Code. On a separate sheet you have a price list with Product Codes in column A and Prices in column B. Rather than manually scanning the price list for each order, VLOOKUP does it with a single formula. For 2,000 rows, that is the difference between an afternoon of copy-pasting and ten seconds of formula work.

The use cases span every industry and role. Pulling customer names from a CRM export given their customer IDs. Looking up tax rates from an income bracket table. Finding a manager's name from an employee directory. Matching sales territory codes to region names. Retrieving unit costs from a procurement table. Any time you have two tables sharing a common identifier and you want to bring data from one into the other, VLOOKUP is the natural first tool to reach for.

VLOOKUP was introduced in Excel 1985 and remains one of the most-used functions in the application nearly four decades later. Its longevity speaks to how fundamental the problem it solves is — but it also has real limitations that Microsoft eventually addressed with XLOOKUP in Excel 365. Understanding both the power and the limits of VLOOKUP is what separates intermediate Excel users from advanced ones.

VLOOKUP Syntax Explained

VLOOKUP takes four arguments. The fourth is technically optional but critically important to include explicitly:

Step 1.Your First VLOOKUP

Scenario: You have an orders sheet with Order IDs in column A. On Sheet2 there is a product table with four columns: ProductID | Product Name | Price | Category. In cell B2 of your orders sheet, you want to pull the Product Name for each order.

Breaking this down argument by argument:

• A2 — the lookup value. This is the Order ID in the current row. VLOOKUP will search for this value in the first column of Sheet2.
• Sheet2!$A:$D — the table array. The entire product table on Sheet2. The dollar signs lock the columns so the reference does not shift when you drag the formula down.
• 2 — the column index. Returns the value from the 2nd column of the table (Product Name). Column 1 would return the ProductID itself; column 3 would return Price; column 4 would return Category.
• 0 — exact match. VLOOKUP only returns a result if the Order ID in A2 is found exactly in column A of Sheet2.

Why Dollar Signs Are Non-Negotiable

When you drag a formula down to fill 2,000 rows, Excel automatically adjusts relative references. A2 becomes A3, then A4 — exactly what you want for the lookup value. But without dollar signs on the table array, the reference shifts too: by row 50 the formula is looking at a range offset 48 columns from your actual data, returning completely wrong values or errors. Always lock your table array with dollar signs.

Once the formula works correctly in B2, grab the fill handle (the small square at the bottom-right of the selected cell) and drag down to apply it to all rows. Excel updates A2 to A3, A4, etc. for each row while keeping the table array locked at Sheet2!$A:$D.

Step 2.Exact Match vs Approximate Match

FALSE (0) — Exact Match

VLOOKUP searches the first column of your table for a row where the value exactly equals your lookup value. If no exact match exists, it returns #N/A. Use exact match for codes, IDs, names, or any lookup where a close-enough match is wrong.

TRUE (1) — Approximate Match

VLOOKUP finds the largest value in the first column that is less than or equal to your lookup value. The first column must be sorted in ascending order for this to work. Use approximate match for bracket-style lookups: tax rates by income, commission tiers by sales amount, grade bands by score.

If ScoreTable has thresholds 0, 60, 70, 80, 90 in column 1 and grades F, D, C, B, A in column 2, a score of 85 returns B — because 85 falls in the 80–89 bracket.

Step 3.IFERROR — Handling #N/A Gracefully

When a lookup value is not found, VLOOKUP returns #N/A. While technically correct, this looks unprofessional in a report and breaks any SUM or AVERAGE formula referencing the column. The solution is wrapping in IFERROR:

Common fallback values depending on context:

Returns blank — the cell appears empty if no match is found.

Returns 0 for numeric lookups (e.g. Price) — safe for SUM formulas downstream.

IFERROR vs IFNA

IFERROR catches all error types: #N/A, #REF!, #VALUE!, #NAME? and others. IFNA catches only #N/A. If you want missing lookups to return your fallback value but still want other error types (such as #REF! from a broken table reference) to surface visibly so you can investigate, use IFNA. For production spreadsheets shared with others, IFNA is often the safer choice.

Step 4.VLOOKUP Across Multiple Sheets and Files

Same Workbook, Different Sheet

Reference the sheet name followed by an exclamation mark before the range. If the sheet name contains spaces, wrap it in single quotes:

Using Named Ranges for Readability

Define a name for your lookup table via Formulas → Name Manager → New (e.g. name it ProductList). Your VLOOKUP becomes self-documenting and survives sheet renames:

Different Workbook

Reference another workbook using square brackets around the filename. The source workbook must be open for a live link; it becomes a static path when closed:

Step 5.Returning Multiple Columns from One VLOOKUP

A common scenario: you need Product Name, Price, and Category from the same table in three adjacent columns. Writing three separate VLOOKUP formulas is repetitive and any change to the table reference requires three edits.

Method 1 — COLUMN() Function (All Excel Versions)

Replace the hard-coded column index with COLUMN(B1). As you drag right, Excel increments B1 to C1, D1, etc., automatically increasing the column index from 2 to 3 to 4. Note the $A2 locking the lookup column when dragging right:

Method 2 — Array Constant (Excel 365)

Use an array constant as the column index. The formula spills results across three adjacent cells automatically:

Method 3 — Switch to INDEX-MATCH or XLOOKUP

Both return ranges of columns natively, without workarounds. If you frequently need multiple return columns, that is a signal to consider the more modern alternative. See the comparison section below.

Step 6.VLOOKUP with Wildcards

VLOOKUP supports wildcard characters in the lookup_value when using exact match, enabling partial text matching. Useful when company names, product codes, or descriptions are formatted inconsistently across tables.

The three wildcards available:

• * — matches any sequence of characters (zero or more)
• ? — matches any single character
• ~ — escapes a literal * or ? character

Contains match (anywhere in the cell)

Finds any row in the first column of CompanyTable that contains the text in A2. Matches "Acme Ltd" when the table has "Acme Limited", for example.

Ends-with match

Step 7.When to Stop Using VLOOKUP

VLOOKUP has served Excel users well for nearly four decades, but it has genuine architectural limitations that no amount of workarounds can fully solve. Knowing when to step up to INDEX-MATCH or XLOOKUP is one of the hallmarks of advanced Excel proficiency.

VLOOKUP's Fundamental Limitation: It Can Only Look Right

The lookup column must be the first column of your table_array. Consider a table with columns Product Name | Product ID | Price. If you want to look up by Product ID and return the Product Name, VLOOKUP cannot do this because Product Name is to the left. You must either rearrange the table (often not possible with shared data sources) or use a different function.

Fragile Column Numbers

The col_index_num argument is a hard-coded integer. If someone inserts a column into your lookup table, every VLOOKUP in the workbook that references columns after the insertion point silently returns values from the wrong column. No error — just wrong data until someone notices. INDEX-MATCH avoids this entirely by referencing the return column directly rather than by position number.

Performance on Large Data

VLOOKUP recalculates whenever any cell in the workbook changes. With thousands of VLOOKUP formulas referencing entire columns on large datasets, calculation delays accumulate. Use specific ranges instead of full-column references, or switch to XLOOKUP which has better performance characteristics on large tables.

When to Use INDEX-MATCH Instead

• You need to look left — the return column is to the left of the lookup column
• Columns may be inserted into the table (INDEX-MATCH uses column references, not numbers)
• You need a two-way lookup by both row and column intersection
• You need better performance on very large datasets

When to Use XLOOKUP Instead (Excel 365 / Excel 2021 only)

• You want the cleanest syntax for new formulas — specify return range directly, no column number
• You need to return multiple adjacent columns without workarounds
• You want built-in error handling without wrapping in IFERROR
• You need last-match behaviour (search from bottom to top)
• You need to look in any direction, including upward

All VLOOKUP Errors Explained

VLOOKUP produces a predictable set of errors. Select an error from the decoder below to see its cause, how to diagnose it, and the corrected formula pattern:

VLOOKUP vs INDEX-MATCH vs XLOOKUP

Understanding when to use each function is more valuable than knowing any single one in isolation. Here is a definitive side-by-side comparison across the eight criteria that matter most in real-world use:

The INDEX-MATCH equivalent

Exactly equivalent to =VLOOKUP(A2, Sheet2!$A:$B, 2, 0) but using column references instead of a hard-coded index number:

The XLOOKUP equivalent

Cleaner than VLOOKUP: specify the lookup range and return range separately. The fallback value is built in — no IFERROR needed: