How to Use VLOOKUP in Excel: Simple Guide with Examples

Use the VLOOKUP function in Excel to search for a value in the first column of a range and return a value from another column in the same row. The syntax is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). It helps you quickly find matching data like prices or names in tables.

Syntax

The VLOOKUP function has four parts:

• lookup_value: The value you want to find in the first column.
• table_array: The range of cells where you want to search.
• col_index_num: The column number in the range to return the value from.
• range_lookup (optional): TRUE for approximate match or FALSE for exact match.

excel

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

Example

This example shows how to find the price of an item using VLOOKUP. Suppose you have a list of items and prices, and you want to find the price of "Apple".

excel

A1: Item    B1: Price
A2: Apple   B2: 1.20
A3: Banana  B3: 0.50
A4: Orange  B4: 0.80

In cell D1, enter: =VLOOKUP("Apple", A1:B4, 2, FALSE)

Output

1.20

Item	Price
Apple	1.20
Banana	0.50
Orange	0.80

Common Pitfalls

Common mistakes when using VLOOKUP include:

• Not setting range_lookup to FALSE for exact matches, causing wrong results.
• Looking up a value not in the first column of the range.
• Using a wrong column index number that is outside the range.
• Forgetting that VLOOKUP searches only left to right.

excel

Wrong: =VLOOKUP("Apple", B2:C4, 2, FALSE)  (lookup column not first)
Right: =VLOOKUP("Apple", A2:B4, 2, FALSE)

Quick Reference

Parameter	Description	Example
lookup_value	Value to find	"Apple"
table_array	Range to search	A2:B4
col_index_num	Column to return	2
range_lookup	Exact or approximate	FALSE (exact)

Key Takeaways

Always set range_lookup to FALSE for exact matches to avoid errors.

VLOOKUP searches only the first column of the range for the lookup value.

The col_index_num must be within the table_array columns.

Use VLOOKUP to quickly find related data in tables by matching values.

Remember VLOOKUP works left to right and cannot look left.