VLOOKUP function in Excel - Step-by-Step Guide

Introduction

VLOOKUP helps you find information in a table quickly. It looks for a value in the first column and returns a matching value from another column in the same row. This saves time when searching for data manually.

When you want to find a price for a product using its ID in a list.

When you need to get a student's grade by looking up their name in a grade sheet.

When you want to match employee names to their departments from a staff list.

When you have a list of parts and want to find their suppliers easily.

When you want to fill in missing data by matching values from another table.

Steps

Step 1: Click

- cell where you want the result

The cell is selected and ready for input

Step 2: Type

- formula bar

Formula input starts

💡 Start with =VLOOKUP(

Step 3: Enter

- formula bar

Type the lookup value, for example, a cell reference like B2

💡 This is the value you want to find in the first column

Step 4: Type

- formula bar

Add a comma, then select the table range where to search, for example, A2:D10

💡 Make sure the first column of this range contains the lookup values

Step 5: Type

- formula bar

Add a comma, then type the column number from which to return the value, for example, 3

💡 Count columns from the left of the table range, starting at 1

Step 6: Type

- formula bar

Add a comma, then type FALSE to find an exact match

💡 Use TRUE or omit for approximate match, but FALSE is safer for exact lookup

Step 7: Type

- formula bar

Close the formula with a parenthesis and press Enter

💡 Example complete formula: =VLOOKUP(B2, A2:D10, 3, FALSE)

Before vs After

Before

A list of product IDs in column A and prices in column C, with an empty cell in D2

After

Cell D2 shows the price for the product ID entered in B2 using VLOOKUP

Settings Reference

Lookup value

📍 First argument in VLOOKUP formula

The value to search for in the first column of the table

Default: No default, must specify

Table array

📍 Second argument in VLOOKUP formula

The range of cells where VLOOKUP searches and returns data

Default: No default, must specify

Column index number

📍 Third argument in VLOOKUP formula

Which column's value to return from the table, counting from left

Default: No default, must specify

Range lookup

📍 Fourth argument in VLOOKUP formula

TRUE for approximate match, FALSE for exact match

Default: TRUE

Common Mistakes

Using a column index number larger than the number of columns in the table range

VLOOKUP cannot return data from a column outside the selected range, causing an error

Make sure the column index number is within the table range columns

Forgetting to use FALSE for exact match when looking up text values

VLOOKUP may return wrong or approximate results if TRUE or omitted

Always use FALSE as the last argument for exact matches

Selecting a table range where the lookup column is not the first column

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

Make sure the lookup column is the first column in the selected range

Summary

VLOOKUP finds a value in the first column of a table and returns a value from another column in the same row.

You must specify the lookup value, table range, column number, and match type (exact or approximate).

Use FALSE for exact matches and ensure the lookup column is the first in the table range.