What is VLOOKUP in Excel?

VLOOKUP in Excel searches the first column of a table for a value and returns a matching value from another column in the same row.

What is the syntax for VLOOKUP in Excel?

The syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Most business lookups should use FALSE for an exact match.

Why does VLOOKUP return #N/A?

VLOOKUP returns #N/A when it cannot find a match, when data types do not match, or when hidden spaces break the lookup.

Is XLOOKUP better than VLOOKUP?

XLOOKUP is better in modern Excel because it works in any direction and returns exact matches by default, but VLOOKUP still matters in older workbooks.

Can VLOOKUP look left?

No. VLOOKUP can only return values from columns to the right of the lookup column inside the selected table array.

Can AI create VLOOKUP formulas for me?

Yes. AI can draft VLOOKUP formulas well, but you should still test a few returned values because spreadsheet joins fail easily on bad keys and messy source data.

VLOOKUP Excel: How to Use It, Fix Errors, and Move to XLOOKUP

Name: Deckary
Rating: 4.8 (50 reviews)
Author: Deckary

VLOOKUP Excel guide with syntax, exact-match examples, cross-sheet formulas, common errors, and clear advice on when to switch to XLOOKUP or AI help at work.

Bob · Former McKinsey and Deloitte consultant with 6 years of experienceMay 9, 20268 min read

VLOOKUP Excel tutorials usually stop at the syntax. That is not the part that breaks in real workbooks. The real issues are using approximate match by accident, pointing at the wrong return column, and joining two lists that only look identical until you notice text-number mismatches or hidden spaces.

For this guide, we reviewed the top 5 Google results for "vlookup excel" on May 9, 2026, checked 5 current Microsoft support pages on VLOOKUP, XLOOKUP, lookup methods, and #N/A fixes, and tested 18 formulas across 6 workbook patterns: exact-match joins, approximate-match banding, cross-sheet lookups, left-lookup constraints, type mismatches, and hidden-space errors.

This guide covers the exact syntax, a business-ready example, the common errors that waste the most time, and where AI tools such as Deckary help when the task is bigger than one formula.

If you need to...	Use this pattern	Important note
Return one value from another table	`=VLOOKUP(A2,$H$2:$J$20,3,FALSE)`	Use `FALSE` for an exact match
Pull data from another sheet	`=VLOOKUP(A2,Products!$A:$D,4,FALSE)`	The lookup key must be in the first column of the selected range
Use a rate band or threshold table	`=VLOOKUP(B2,$M$2:$N$8,2,TRUE)`	Approximate match only works when the first column is sorted
Return a value from the left	Use `XLOOKUP` or `INDEX` + `MATCH`	`VLOOKUP` cannot look left
Fix repeated lookup errors	Check types, spaces, and match mode	Most `#N/A` issues are not syntax issues

VLOOKUP Excel quick reference infographic

What VLOOKUP in Excel Does#

VLOOKUP in Excel searches the first column of a selected range and returns a value from another column in the same row. Microsoft's VLOOKUP function documentation says the lookup value must sit in the first column of the table_array, and the return value comes from a numbered column inside that same range.

That makes VLOOKUP a join tool:

Use case	Example
Match IDs to names	Return account owner from an employee ID
Match SKUs to attributes	Return product category or price
Match codes to labels	Turn status codes into readable labels
Apply a sorted threshold table	Return a discount, tax rate, or commission band

Microsoft's lookup overview page still lists VLOOKUP, INDEX, and MATCH among Excel's most useful lookup methods.

VLOOKUP Excel Syntax and the Four Arguments#

VLOOKUP Excel syntax is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Microsoft's VLOOKUP support page uses the same four-part syntax:

Argument	What it means	Analyst rule
`lookup_value`	The value you want to find	Usually an ID, SKU, account code, or name
`table_array`	The range that contains both the lookup key and the return column	Start the range where the key lives
`col_index_num`	The numbered return column inside that selected range	Count from the left edge of the selected range, not from column A
`range_lookup`	`FALSE` for exact match or `TRUE` for approximate match	Use `FALSE` unless you intentionally want banded logic

Two details cause most mistakes: col_index_num counts inside the selected range, not the whole worksheet, and a blank range_lookup defaults to approximate match. That is why many analysts treat FALSE as mandatory for business joins.

How to Use VLOOKUP in Excel for an Exact Match#

The most common VLOOKUP job is a straight exact match between two tables. Imagine column A contains employee IDs and a lookup table in H:J contains ID, manager, and cost center.

Use this formula:

=VLOOKUP(A2,$H$2:$J$20,3,FALSE)

Here is the same logic in business terms:

Cell	Meaning
`A2`	Employee ID you want to match
`$H$2:$J$20`	Lookup table with ID in the first column
`3`	Return the cost center from the third column in that table
`FALSE`	Only accept an exact ID match

If you need to copy the formula down, lock the lookup range with absolute references as shown above. That prevents the lookup table from drifting row by row.

Continue reading: PowerPoint Master Slide · Best Fonts for PowerPoint · AI for Excel

Build and audit Excel workbooks with AI

Describe the model, cleanup, audit, or output sheet you need. Deckary's AI Excel agent works through the task inside your workbook.

Try AI Excel Agent See How It Works

How to Use VLOOKUP Across Sheets and for Approximate Matches#

VLOOKUP across sheets uses the same logic. You just point the table_array at another tab:

=VLOOKUP(A2,Products!$A:$D,4,FALSE)

That formula looks up the value in A2, searches the Products sheet range A:D, and returns the fourth column. This is the standard pattern for matching a raw export on one tab against a reference table on another.

Approximate match is different. Microsoft's lookup guide explains that when you use TRUE, or leave the fourth argument blank, VLOOKUP returns the closest lower match. That is useful for tax bands, score bands, discount schedules, and other sorted threshold tables.

Example:

=VLOOKUP(B2,$M$2:$N$8,2,TRUE)

Use approximate match only when the first column is sorted and the business logic really is threshold-based. If you are matching IDs, invoice numbers, account names, or SKUs, it is usually the wrong choice.

Common VLOOKUP Errors and How to Fix Them#

Most VLOOKUP failures are data issues, not formula issues. Microsoft's How to correct a #N/A error page says the most common cause is simple: the formula cannot find the referenced value. The same page also calls out mismatched data types, hidden spaces, and the exact-vs-approximate-match setting.

Symptom	Likely cause	Fix
`#N/A`	No exact match exists	Check whether the lookup value is really in the source table
`#N/A`	Numbers stored as text, or text stored as numbers	Align both columns to the same data type
`#N/A`	Leading or trailing spaces	Use `TRIM`, or clean the source data first
Wrong but non-error result	`TRUE` used by accident	Change the fourth argument to `FALSE`
`#REF!`	`col_index_num` is larger than the selected range	Recount columns from the left edge of `table_array`
Formula breaks after copy-paste	Relative range moved	Lock the lookup range with `$`

One practical pattern helps with user-facing outputs:

=IFERROR(VLOOKUP(A2,$H$2:$J$20,3,FALSE),"Not found")

Use IFERROR for dashboards and final deliverables. Do not use it to hide issues while you are still validating the join.

VLOOKUP Excel vs XLOOKUP#

VLOOKUP Excel still matters, but XLOOKUP is the better default in modern Excel. Microsoft's XLOOKUP documentation says it can return results from either direction and uses exact match by default.

Dimension	VLOOKUP	XLOOKUP
Match default	Approximate if omitted	Exact by default
Can look left	No	Yes
Return column method	Numeric index inside a table range	Direct return array
Missing-value handling	Add `IFERROR` or similar	Built-in `if_not_found` argument
Multi-column returns	No	Yes
Older workbook compatibility	Better	Depends on version

Microsoft also notes that XLOOKUP is not available in Excel 2016 and Excel 2019, though those versions can open workbooks created in newer versions. Use XLOOKUP for new workbooks when your environment supports it, keep VLOOKUP for inherited files, and use INDEX + MATCH when you need backward-compatible left lookups.

When to Ask AI for Help With VLOOKUP#

AI is useful for VLOOKUP when you know the business logic but do not want to hand-type the formula. Microsoft and LinkedIn's 2024 Work Trend Index surveyed 31,000 people across 31 countries and reported that 75% of knowledge workers already use AI at work.

Verification still matters. Raymond Panko's 2008 paper "Spreadsheet Errors: What We Know. What We Think We Can Do" says 15 years of research had already found spreadsheet errors to be common and non-trivial. The EMNLP 2023 paper "InstructExcel: A Benchmark for Natural Language Instruction in Excel" introduced a benchmark with over 10,000 samples, 170-plus Excel operations, and 2,000 public spreadsheets, and described it as hard even for GPT-4.

Ask AI to draft the formula or explain the syntax, then verify the output on known rows. If the real job is workbook-wide, such as creating hundreds of lookup formulas across tabs and checking that returned values reconcile cleanly, a workbook agent such as Deckary is a better fit because it can write the formulas in-place and verify the result. For adjacent workflows, read Best AI for Excel, Excel AI Agent, and ChatGPT for Excel.

Summary#

Use FALSE for most business VLOOKUP formulas.
Keep the lookup key in the first column of the selected range.
Lock the lookup range before copying formulas down.
Check data types and hidden spaces before you blame the syntax.
Use XLOOKUP for new work when your Excel version supports it.
Use AI to draft or explain formulas, then verify the returned values on sample rows.