Software Guides Microsoft Office Guide Excel Guide

VLOOKUP Function in Excel: Ultimate Guide

August 8, 2026

22

VLOOKUP Function in Excel - Ultimate Guide

VLOOKUP is one of Excel’s most useful lookup functions. It searches for a value in the first column of a table and returns a value from the same row in another column. This guide covers syntax, exact vs approximate match, examples, common errors, limitations, and modern alternatives like XLOOKUP and INDEX/MATCH.

1. What Does VLOOKUP Do?

VLOOKUP stands for Vertical Lookup.

It works like this:

You give Excel a value to find.
Excel searches for that value in the first column of a range.
When it finds a match, it returns a value from a column you specify.

Simple Example

	A	B	C
1	ID	Product	Price
2	101	Pen	2.50
3	102	Notebook	4.00
4	103	Eraser	1.25

Formula:

=VLOOKUP(102, A2:C4, 3, FALSE)

Result:

4.00

Explanation:

Look for 102
Search in the first column of A2:C4
Return the value from column 3
Use exact match

VLOOKUP Syntax

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

Argument	Required?	Description
`lookup_value`	Yes	The value you want to find.
`table_array`	Yes	The range containing the lookup and return columns.
`col_index_num`	Yes	The column number in `table_array` from which to return a value.
`range_lookup`	Optional	`FALSE` for exact match. `TRUE` for approximate match. If omitted, Excel assumes `TRUE`.

Important VLOOKUP Rules

Rule 1: VLOOKUP searches only in the first column

If your lookup value is in column C, VLOOKUP cannot directly search it unless you rearrange the data or use a workaround.

Rule 2: Column numbering starts at 1

In this range:

A2:C100

Column A = 1
Column B = 2
Column C = 3

So:

=VLOOKUP(101, A2:C100, 3, FALSE)

returns a value from column C.

Rule 3: Use FALSE for exact match in most cases

=VLOOKUP(A2, B2:D100, 2, FALSE)

If you omit FALSE, Excel uses approximate match by default, which can give unexpected results.

Exact Match vs Approximate Match

Exact Match: FALSE

Use exact match when you want to find the exact lookup value.

=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

Best for:

Product IDs
Employee IDs
Invoice numbers
Names
Codes
SKUs

Example:

=VLOOKUP(103, A2:C4, 2, FALSE)

Result:

Eraser

If Excel does not find an exact match, it returns:

#N/A

Approximate Match: TRUE

Use approximate match when you want Excel to find the closest match.

=VLOOKUP(lookup_value, table_array, col_index_num, TRUE)

Important:

The first column must be sorted in ascending order.
If an exact match is not found, Excel returns the largest value that is less than the lookup value.

Approximate match is useful for:

Commission tiers
Tax brackets
Grade boundaries
Discount levels
Shipping rate tables

Example

	F	G
1	Sales	Commission
2	0	0%
3	1000	5%
4	5000	10%
5	10000	15%

Formula:

=VLOOKUP(7000, F2:G5, 2, TRUE)

Result:

10%

Why?

7000 is between 5000 and 10000.
Excel finds the largest value less than or equal to 7000, which is 5000.
It returns the corresponding commission: 10%.

Basic VLOOKUP Examples

Assume this table:

	A	B	C	D
1	ID	Product	Price	Stock
2	101	Pen	2.50	100
3	102	Notebook	4.00	50
4	103	Eraser	1.25	200
5	104	Ruler	1.75	80

Example 1: Lookup a number

=VLOOKUP(102, A2:D5, 2, FALSE)

Returns:

Notebook

Example 2: Lookup using a cell reference

If cell F1 contains 103:

=VLOOKUP(F1, A2:D5, 3, FALSE)

Returns:

1.25

Example 3: Return price

=VLOOKUP(F1, A2:D5, 3, FALSE)

Column 3 is the Price column.

Example 4: Return stock

=VLOOKUP(F1, A2:D5, 4, FALSE)

Column 4 is the Stock column.

Example 5: Lookup from another sheet

If your data is on Sheet2:

=VLOOKUP(A2, Sheet2!A2:D100, 3, FALSE)

If the data is in another workbook:

=VLOOKUP(A2, '[Price List.xlsx]Products'!A2:D100, 3, FALSE)

Practical VLOOKUP Examples

Example 1: Find a Product Price

	A	B	C
1	ID	Product	Price
2	101	Pen	2.50
3	102	Notebook	4.00
4	103	Eraser	1.25

Formula:

=VLOOKUP(102, A2:C4, 3, FALSE)

Result:

4.00

Example 2: Lookup Employee Department

	A	B	C
1	EmpID	Name	Department
2	E001	Alice	Sales
3	E002	Bob	Finance
4	E003	Carol	Marketing

Formula:

=VLOOKUP("E002", A2:C4, 3, FALSE)

Result:

Finance

Example 3: Commission or Tax Bracket Lookup

	F	G
1	Sales	Rate
2	0	0%
3	1000	5%
4	5000	10%
5	10000	15%

If sales are in cell B2:

=VLOOKUP(B2, F2:G5, 2, TRUE)

Make sure the first column is sorted ascending:

Example 4: Grade Lookup

	F	G
1	Score	Grade
2	0	F
3	60	D
4	70	C
5	80	B
6	90	A

Formula:

=VLOOKUP(85, F2:G6, 2, TRUE)

Result:

Because 85 is between 80 and 90.

Example 5: Dynamic Column Number Using MATCH

Instead of hard-coding the column number, use MATCH to find it.

Suppose your headers are in row 1:

A	B	C	D
ID	Product	Price	Stock

You want to look up the value in cell F1 and return the column named in cell G1.

Formula:

=VLOOKUP(F1, A1:D100, MATCH(G1, A1:D1, 0), FALSE)

Example:

F1 = 102
G1 = Price

MATCH(G1, A1:D1, 0) returns 3, because Price is the third column.

So the formula becomes:

=VLOOKUP(102, A1:D100, 3, FALSE)

This is useful because the formula will not break if columns are moved, as long as the header names stay the same.

Example 6: Two-Way Lookup

You can combine VLOOKUP with MATCH to lookup both by row and column.

Data

	A	B	C	D
1	ID	Jan	Feb	Mar
2	101	500	600	700
3	102	300	400	250
4	103	800	900	950

You want:

Row lookup: ID in F1
Column lookup: Month in G1

Formula:

=VLOOKUP($F$1, $A$1:$D$4, MATCH($G$1, $A$1:$D$1, 0), FALSE)

Example:

F1 = 102
G1 = Feb

Result:

Example 7: Partial Text Lookup Using Wildcards

VLOOKUP supports wildcards when using exact match.

Wildcard	Meaning
`*`	Matches any number of characters
`?`	Matches one character
`~`	Escapes a wildcard character

Example

Find a product name containing the text in F1.

=VLOOKUP("*" & F1 & "*", A2:C100, 2, FALSE)

If F1 contains:

note

Then Excel searches for:

*note*

This can match:

Notebook
Note Pad
Sticky Notes

Example 8: Search for Literal Wildcards

If your text contains an actual asterisk, use ~*.

Example:

=VLOOKUP("~*", A2:C100, 2, FALSE)

This searches for:

not as a wildcard, but as a literal character.

Example 9: VLOOKUP with Multiple Criteria Using a Helper Column

VLOOKUP cannot directly handle multiple criteria well, but you can create a helper key.

Source Data

	A	B	C	D
1	Region	Product	Sales	Price
2	East	Pen	100	2.50
3	West	Pen	120	2.75
4	East	Ruler	80	1.75

Create a helper column:

=A2 & "|" & B2

Example result:

East|Pen

Then use:

=VLOOKUP(F1 & "|" & G1, A:E, 5, FALSE)

Where:

F1 contains Region
G1 contains Product

Example:

F1 = East
G1 = Pen

Formula:

=VLOOKUP("East|Pen", A:E, 5, FALSE)

Returns the matching price.

Example 10: VLOOKUP with an Excel Table

If your data is formatted as an Excel table named Products, VLOOKUP becomes easier and more dynamic.

Assume the table has columns:

ID
Product
Price

Formula:

=VLOOKUP(A2, Products[[ID]:[Price]], 2, FALSE)

This looks up the ID in cell A2 and returns the Product name.

If you want Price:

=VLOOKUP(A2, Products[[ID]:[Price]], 3, FALSE)

Using Excel Tables is recommended because the reference expands automatically when new rows are added.

Example 11: Return Multiple Columns in Microsoft 365

In Microsoft 365 or Excel with dynamic arrays, you can use an array constant:

=VLOOKUP(A2, A2:D100, {2,3,4}, FALSE)

This can return columns 2, 3, and 4 side by side.

Example:

=VLOOKUP(102, A2:D5, {2,3,4}, FALSE)

May return:

Notebook    4.00    50

In older versions of Excel, you may need separate formulas for each column.

Handling VLOOKUP Errors

Use IFNA

If you want to show a friendly message when the value is not found:

=IFNA(VLOOKUP(A2, B2:D100, 2, FALSE), "Not Found")

Use IFERROR

If you want to catch all errors:

=IFERROR(VLOOKUP(A2, B2:D100, 2, FALSE), "Error")

However, IFERROR can hide other problems, such as incorrect column numbers.

For lookup-specific missing values, IFNA is often better.

Common VLOOKUP Errors and Fixes

#N/A

Meaning

Excel could not find the lookup value.

Common Causes

The value does not exist.
You are using exact match, but the lookup value has extra spaces.
Numbers are stored as text.
The lookup column is not the first column.
You are using approximate match with unsorted data.
The lookup value is smaller than the first value in approximate match.

Fixes

Check the value manually.

Use TRIM:

=VLOOKUP(TRIM(A2), B2:D100, 2, FALSE)

Use IFNA:

=IFNA(VLOOKUP(A2, B2:D100, 2, FALSE), "Not Found")

For numbers stored as text, convert the data to real numbers or use a helper column.

#REF!

Meaning

The column index number is outside the table range.

Example

=VLOOKUP(A2, B2:C100, 4, FALSE)

The range B2:C100 only has two columns, but the formula asks for column 4.

Fix

Reduce the column index number or expand the range.

Correct example:

=VLOOKUP(A2, B2:E100, 4, FALSE)

#VALUE!

Meaning

Something is wrong with the formula arguments.

Common Causes

col_index_num is less than 1.
range_lookup is not TRUE, FALSE, 1, or 0.
The lookup range is invalid.

Fix

Make sure the syntax is correct:

=VLOOKUP(A2, B2:D100, 2, FALSE)

#NAME?

Meaning

Excel does not recognize something in the formula.

Common Causes

Misspelled function name:

=VLOKUP(A2, B2:D100, 2, FALSE)

Misspelled named range:

=VLOOKUP(A2, ProductListt, 2, FALSE)

Fix

Check spelling of:

Function name
Sheet names
Named ranges
Table names

VLOOKUP Returns the Wrong Value

Common Causes

You omitted FALSE and Excel is using approximate match.
The first column is not sorted when using approximate match.
The lookup value matches the first duplicate.
The lookup column contains numbers stored as text.
There are hidden spaces.

Fix

Usually, use exact match:

=VLOOKUP(A2, B2:D100, 2, FALSE)

Why VLOOKUP Returns the First Match Only

If your lookup column contains duplicates, VLOOKUP returns the first match it finds.

Example:

A	B
101	First
101	Second
101	Third

Formula:

=VLOOKUP(101, A2:B4, 2, FALSE)

Returns:

First

If you need all matches, use FILTER in Microsoft 365:

=FILTER(B2:B4, A2:A4=101)

VLOOKUP Limitations

Limitation 1: VLOOKUP Cannot Look Left

VLOOKUP searches only in the first column of the table.

It can return columns to the right, but not to the left.

Workarounds

Use one of these:

Rearrange your columns.
Use XLOOKUP.
Use INDEX and MATCH.
Use CHOOSE to create a virtual lookup table.

Limitation 2: VLOOKUP Is Not Case-Sensitive

VLOOKUP treats these as the same:

ABC
abc
Abc

If you need a case-sensitive lookup, use INDEX and MATCH with EXACT.

Example:

=INDEX(C2:C100, MATCH(TRUE, EXACT(A2:A100, F2), 0))

In older versions of Excel, this may require pressing:

Ctrl + Shift + Enter

Limitation 3: Hard-Coded Column Numbers Can Break

This formula uses column 3:

=VLOOKUP(A2, B2:D100, 3, FALSE)

If someone inserts a column inside the lookup range, column 3 may no longer be the column you want.

Better formula:

=VLOOKUP(A2, B2:Z100, MATCH("Price", B1:Z1, 0), FALSE)

Limitation 4: VLOOKUP Does Not Handle Multiple Criteria Naturally

VLOOKUP is designed for one lookup value.

For multiple criteria, use:

Helper column
INDEX and MATCH
XLOOKUP
FILTER
Power Query
PivotTables
Data Model relationships

Limitation 5: Approximate Match Can Be Dangerous

If you use:

=VLOOKUP(A2, B2:D100, 2)

or:

=VLOOKUP(A2, B2:D100, 2, TRUE)

the first column must be sorted ascending.

If it is not sorted, VLOOKUP can return an incorrect value without showing an error.

VLOOKUP Workarounds

Looking Left Using CHOOSE

Suppose:

Lookup value is in column C.
Return value is in column A.

Normally, VLOOKUP cannot do this.

But you can create a virtual table:

=VLOOKUP(F2, CHOOSE({1,2}, C2:C100, A2:A100), 2, FALSE)

This creates:

Column 1: C2:C100
Column 2: A2:A100

Then VLOOKUP can search column C and return column A.

However, XLOOKUP or INDEX/MATCH is usually easier.

VLOOKUP vs XLOOKUP vs INDEX/MATCH

VLOOKUP

=VLOOKUP(A2, A2:D100, 3, FALSE)

Pros

Easy to learn.
Widely used.
Works in almost all Excel versions.

Cons

Cannot look left.
Column index numbers can break.
Approximate match default can cause errors.
Not case-sensitive.
Limited with multiple criteria.

XLOOKUP

=XLOOKUP(A2, A2:A100, C2:C100, "Not Found")

Pros

Simpler syntax.
Default is exact match.
Can look left or right.
Can search from bottom to top.
Built-in error handling.
Better replacement for VLOOKUP and HLOOKUP.

Cons

Not available in older Excel versions.

INDEX/MATCH

=INDEX(C2:C100, MATCH(A2, A2:A100, 0))

Pros

Can look left or right.
More flexible than VLOOKUP.
Works in older Excel versions.
Better for advanced formulas.

Cons

More complex to learn.
Requires two functions.

Comparison Table

Feature	VLOOKUP	XLOOKUP	INDEX/MATCH
Easy for beginners	Yes	Yes	Moderate
Exact match by default	No	Yes	Depends on MATCH
Can look left	No	Yes	Yes
Can look right	Yes	Yes	Yes
Built-in error message	No	Yes	No
Works in older Excel	Yes	No	Yes
Handles horizontal lookup	No, use HLOOKUP	Yes	Yes
Best modern option	No	Yes	Yes for compatibility

Recommended Modern Alternative: XLOOKUP

If you have Microsoft 365, Excel 2021, or newer, use XLOOKUP when possible.

Basic XLOOKUP

Instead of:

=VLOOKUP(A2, A2:D100, 3, FALSE)

Use:

=XLOOKUP(A2, A2:A100, C2:C100)

This is often easier to read.

XLOOKUP with Custom Not Found Message

=XLOOKUP(A2, A2:A100, C2:C100, "Not Found")

No need for IFNA or IFERROR.

XLOOKUP Looking Left

If lookup values are in column C and return values are in column A:

=XLOOKUP(F2, C2:C100, A2:A100)

VLOOKUP cannot do this directly.

Recommended Alternative for Older Excel: INDEX/MATCH

If you do not have XLOOKUP, use INDEX/MATCH.

Basic INDEX/MATCH

Instead of:

=VLOOKUP(A2, A2:D100, 3, FALSE)

Use:

=INDEX(C2:C100, MATCH(A2, A2:A100, 0))

Explanation:

MATCH(A2, A2:A100, 0) finds the row number.
INDEX(C2:C100, ...) returns the value from column C.

INDEX/MATCH Looking Left

=INDEX(A2:A100, MATCH(F2, C2:C100, 0))

This looks up a value in column C and returns a value from column A.

VLOOKUP Best Practices

1. Use FALSE for exact match

=VLOOKUP(A2, B2:D100, 2, FALSE)

Do not omit the last argument unless you intentionally want approximate match.

2. Lock the lookup range

When copying formulas down, use absolute references:

=VLOOKUP(A2, $B$2:$D$100, 2, FALSE)

Or use an Excel Table.

3. Use Excel Tables

Instead of:

=VLOOKUP(A2, Sheet2!$A$2:$D$500, 3, FALSE)

use a named table:

=VLOOKUP(A2, Products[[ID]:[Price]], 3, FALSE)

Tables expand automatically.

4. Avoid approximate match unless needed

Use approximate match only for sorted bracket-style tables.

Examples:

Tax rates
Commission rates
Grades
Discounts
Shipping rates

5. Use MATCH instead of hard-coded column numbers

Instead of:

=VLOOKUP(A2, B2:Z100, 5, FALSE)

use:

=VLOOKUP(A2, B2:Z100, MATCH("Price", B1:Z1, 0), FALSE)

6. Handle #N/A cleanly

Use:

=IFNA(VLOOKUP(A2, B2:D100, 2, FALSE), "Not Found")

7. Clean your data

Common issues:

Extra spaces
Numbers stored as text
Different date formats
Hidden characters
Inconsistent capitalization
Duplicate lookup values

Useful functions:

=TRIM(A2)
=CLEAN(A2)
=VALUE(A2)
=TEXT(A2, "0")

8. Use unique lookup keys when possible

VLOOKUP works best when the lookup column contains unique values.

Good lookup columns:

Product ID
Employee ID
Invoice number
Order number
SKU
Barcode

Poor lookup columns:

First name
City
Department
Category
Status

VLOOKUP with Numbers Stored as Text

This is a very common problem.

If your lookup value is numeric:

but the lookup column contains text:

"101"

VLOOKUP may return #N/A.

Fix 1: Convert source data to numbers

Select the column, then use:

Text to Columns
Paste Special > Multiply by 1
VALUE function
Error checking options

Fix 2: Convert lookup value to text

=VLOOKUP(TEXT(A2, "0"), B2:D100, 2, FALSE)

Fix 3: Convert lookup value to number

=VLOOKUP(VALUE(A2), B2:D100, 2, FALSE)

Use the approach that matches your source data type.

VLOOKUP with Extra Spaces

Extra spaces can cause exact match to fail.

Example:

"Pen"
"Pen "
" Pen"

These look similar but are not identical.

Use:

=VLOOKUP(TRIM(A2), B2:D100, 2, FALSE)

However, if the source data has spaces, you may need to clean the source data too.

VLOOKUP with Dates

VLOOKUP works with dates, but dates must be stored as real Excel dates.

Example:

A	B
2025-01-01	100
2025-02-01	120
2025-03-01	130

Formula:

=VLOOKUP(DATE(2025,2,1), A2:B4, 2, FALSE)

If approximate match is used, sort dates ascending.

VLOOKUP with Approximate Match and Dates

For date ranges, use approximate match.

Example:

F	G
Start Date	Rate
2025-01-01	Low
2025-02-01	Medium
2025-03-01	High

Formula:

=VLOOKUP(H1, F2:G4, 2, TRUE)

If H1 is:

2025-02-15

Result:

Medium

The Start Date column must be sorted ascending.

VLOOKUP Across Multiple Sheets

You can reference another sheet:

=VLOOKUP(A2, Sheet2!A2:D100, 3, FALSE)

If the sheet name has spaces:

=VLOOKUP(A2, 'Price List'!A2:D100, 3, FALSE)

If the file is closed:

=VLOOKUP(A2, 'C:\Reports\[Prices.xlsx]Sheet1'!A2:D100, 3, FALSE)

VLOOKUP with Named Ranges

Suppose you define a named range called ProductData.

Formula:

=VLOOKUP(A2, ProductData, 2, FALSE)

This can make formulas easier to read.

However, if you add rows, a normal named range may not expand automatically unless it uses a dynamic formula or an Excel Table.

VLOOKUP with Excel Tables and Structured References

If you have a table named Sales, you can use structured references.

Example:

=VLOOKUP(A2, Sales[[Product]:[Amount]], 2, FALSE)

This looks up the product name and returns the amount.

Using tables is one of the most reliable ways to keep lookups dynamic.

VLOOKUP Performance Tips

For small workbooks, VLOOKUP is usually fine.

For large workbooks, consider these tips.

Use exact match when needed

=VLOOKUP(A2, B2:D100000, 2, FALSE)

Use approximate match only when appropriate

Approximate match can be faster if the first column is sorted.

Avoid unnecessary whole-column references

This may be slower:

=VLOOKUP(A2, B:D, 2, FALSE)

Better:

=VLOOKUP(A2, B2:D100000, 2, FALSE)

Or use a table.

Avoid repeated VLOOKUPs

If you need several values from the same row, consider:

XLOOKUP
INDEX/MATCH
Power Query
Data Model relationships

VLOOKUP vs Power Query

If you are doing large or repeated lookups, Power Query may be better.

Use Power Query when:

You have large tables.
You need to combine multiple tables.
You want repeatable transformations.
You need left joins, right joins, or full outer joins.
You want to avoid formula maintenance.

In Power Query, the equivalent concept is usually Merge Queries.

Common VLOOKUP Mistakes

Mistake 1: Forgetting FALSE

Wrong:

=VLOOKUP(A2, B2:D100, 2)

Better:

=VLOOKUP(A2, B2:D100, 2, FALSE)

Mistake 2: Lookup column is not first

Wrong:

=VLOOKUP(A2, C2:E100, 2, FALSE)

If A2 is not in column C, it will not work.

Mistake 3: Wrong column index

Wrong:

=VLOOKUP(A2, B2:D100, 5, FALSE)

The range only has three columns.

Mistake 4: Not locking the range

When copying down:

=VLOOKUP(A2, B2:D100, 2, FALSE)

may become:

=VLOOKUP(A3, B3:D101, 2, FALSE)

Use:

=VLOOKUP(A2, $B$2:$D$100, 2, FALSE)

Mistake 5: Using approximate match with unsorted data

If you use:

=VLOOKUP(A2, B2:D100, 2, TRUE)

the first column must be sorted ascending.

VLOOKUP Cheat Sheet

Exact match

=VLOOKUP(lookup_value, table_array, column_number, FALSE)

Approximate match

=VLOOKUP(lookup_value, table_array, column_number, TRUE)

Lookup from another sheet

=VLOOKUP(A2, Sheet2!A2:D100, 3, FALSE)

Handle #N/A

=IFNA(VLOOKUP(A2, B2:D100, 2, FALSE), "Not Found")

Dynamic column number

=VLOOKUP(A2, B2:Z100, MATCH("Price", B1:Z1, 0), FALSE)

Two-way lookup

=VLOOKUP($F$1, $A$1:$D$100, MATCH(G$1, $A$1:$D$1, 0), FALSE)

Wildcard lookup

=VLOOKUP("*" & A2 & "*", B2:D100, 2, FALSE)

Modern replacement

=XLOOKUP(A2, A2:A100, C2:C100, "Not Found")

When to Avoid VLOOKUP

Avoid VLOOKUP when:

You need to look left.
You need case-sensitive lookup.
You need multiple criteria.
You need all matching records.
You need a highly flexible solution.
You are working with large data models.
You want modern, cleaner formulas.

Use instead:

XLOOKUP
INDEX/MATCH
FILTER
Power Query
PivotTables
Data Model relationships

Summary

VLOOKUP is a powerful and widely used Excel function, but it has important limitations.

The most important rules are:

Use FALSE for exact match.
Put the lookup value in the first column.
Count the return column correctly.
Sort data ascending if using approximate match.
Handle #N/A with IFNA when appropriate.
Use XLOOKUP or INDEX/MATCH when you need more flexibility.

A good default formula is:

=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

But if you have a modern version of Excel, this is often better:

=XLOOKUP(lookup_value, lookup_array, return_array, "Not Found")