How to Use VLOOKUP in Excel

Introduction

You get a spreadsheet with hundreds of product IDs in one column and need to pull the matching product name from a reference table. Typing each one by hand is out of the question. VLOOKUP searches for a value in the first column of a table and returns a value from another column in the same row.

The Dataset

Here's a product catalog:

	A	B	C	D
3	ID	Product	Category	Price
4	101	Laptop Stand	Accessories	120
5	102	USB Hub	Accessories	45
6	103	Monitor Arm	Furniture	200
7	104	Desk Mat	Accessories	30
8	105	Cable Kit	Accessories	85

The purchasing clerk needs to pull the product name and price for each ID on an incoming order before invoicing.

A Basic Lookup

The syntax is:

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

lookup_value: The value to search for. It must appear in the first column of table_array.
table_array: The range containing the data.
col_index_num: Which column to return a value from. 1 is the first column, 2 is the second, and so on.
range_lookup: Optional. FALSE for an exact match, TRUE (or omitted) for an approximate match. Use FALSE in most cases.

To find the product name for ID 103:

=VLOOKUP(103, A4:D8, 2, FALSE)

103 is the value to search for.
A4:D8 is the table range.
2 means return the value from the second column (Product).
FALSE requires an exact match.

The result is Monitor Arm.

To get the price instead, change the column number:

=VLOOKUP(103, A4:D8, 4, FALSE)

The result is 200. Same lookup, different column.

Running the product name lookup for every ID:

ID	Product	Price
101	Laptop Stand	120
102	USB Hub	45
103	Monitor Arm	200
104	Desk Mat	30
105	Cable Kit	85

If the ID comes from another cell instead of being typed into the formula, reference that cell. With the ID in F4:

=VLOOKUP(F4, $A$4:$D$8, 2, FALSE)

The dollar signs lock the table range so it doesn't shift when you copy the formula down.

Exact vs Approximate Match

The fourth argument controls how VLOOKUP searches.

FALSE (exact match) looks for the lookup value exactly. If it doesn't find it, you get #N/A. This is what you want when looking up IDs, names, or codes.

TRUE or omitted (approximate match) finds the largest value less than or equal to the lookup value. The first column must be sorted in ascending order for this to work.

Approximate match is useful for range-based lookups like tax brackets:

	A	B
3	Income	Rate
4	0	10%
5	10000	12%
6	40000	22%
7	85000	24%

To find the rate for an income of 55,000:

=VLOOKUP(55000, A4:B7, 2, TRUE)

55,000 falls between 40,000 and 85,000, so VLOOKUP returns the rate for the 40,000 row: 22%.

Key takeaway: Always use FALSE unless you're doing a range-based lookup with sorted data. Leaving out the fourth argument defaults to TRUE, which is the most common source of wrong VLOOKUP results.

When Columns Shift

The col_index_num argument is a number, not a column reference. If you insert or delete a column inside the table range, that number doesn't update. A formula that returns column 4 will still return column 4 even if that column now holds different data.

Say you insert a new column between Category and Price. What was column 4 (Price) is now column 5. Your formula still says 4, so it returns Category instead.

One workaround is to use MATCH for the column number:

=VLOOKUP(103, A4:E8, MATCH("Price", A3:E3, 0), FALSE)

MATCH finds "Price" in the header row and returns its position. If columns move, MATCH adjusts with them.

Limitations

Left-to-right only. The lookup value must be in the first column of the table range. If the value you want to return is to the left of the value you're searching, VLOOKUP can't do it.
One return value. Each formula returns a single cell. It can't return an entire row or multiple columns at once.
First match wins. If the lookup value appears more than once, VLOOKUP returns the first match. There's no way to get the second or third.
Column index fragility. The column number is hardcoded and doesn't adjust when columns are inserted or deleted, as covered above.

VLOOKUP works in every version of Excel. If you're on a newer version and want to avoid these constraints, XLOOKUP and INDEX/MATCH are worth learning.

Conclusion

VLOOKUP searches the first column of a range and returns a value from whichever column you specify. It handles exact lookups for IDs and codes, and approximate lookups for brackets and tiers.

For a newer alternative that removes the left-to-right restriction and doesn't use column numbers, see our guide on XLOOKUP. For a flexible approach that works in any Excel version, check out our guide on INDEX and MATCH.