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.

Practice

1. What does the VLOOKUP function do in Excel?

easy

A. It sorts data alphabetically.

B. It adds up all numbers in a column.

C. It creates a chart from selected data.

D. It looks for a value in the first column of a table and returns a value from the same row.

Solution

1. Step 1: Understand VLOOKUP purpose

   VLOOKUP searches for a value in the first column of a table.

2. Step 2: Identify what it returns

   It returns a value from the same row but a different column you specify.

3. Final Answer:

   It looks for a value in the first column of a table and returns a value from the same row. -> Option D

4. Quick Check:

   VLOOKUP finds and returns matching row data [OK]

Hint: VLOOKUP always searches first column, returns from chosen column [OK]

Common Mistakes:

• Thinking VLOOKUP sums or sorts data
• Confusing VLOOKUP with chart tools
• Assuming it searches any column, not just first

2. Which of these is the correct syntax for an exact match VLOOKUP formula to find value in A2 in table B2:D10 and return column 3?

easy

A. =VLOOKUP(A2, B2:D10, 2, FALSE)

B. =VLOOKUP(A2, B2:D10, 3, TRUE)

C. =VLOOKUP(A2, B2:D10, 3, FALSE)

D. =VLOOKUP(A2, B2:D10, 3)

Solution

1. Step 1: Identify exact match argument

   For exact match, the fourth argument must be FALSE.

2. Step 2: Check column index and range

   We want column 3 from range B2:D10, so column index is 3.

3. Final Answer:

   =VLOOKUP(A2, B2:D10, 3, FALSE) -> Option C

4. Quick Check:

   Exact match needs FALSE as last argument [OK]

Hint: Use FALSE for exact match, TRUE for approximate [OK]

Common Mistakes:

• Using TRUE instead of FALSE for exact match
• Forgetting the column number or using wrong one
• Omitting the last argument defaults to TRUE (approximate)

3. Given this table in cells A1:B4:

Name | Score
John | 85
Mary | 92
Alex | 78

What is the result of =VLOOKUP("Mary", A2:B4, 2, FALSE)?

medium

A. 85

B. 92

C. Mary

D. #N/A

Solution

1. Step 1: Locate lookup value in first column

   "Mary" is found in cell A3.

2. Step 2: Return value from column 2 same row

   Column 2 in row 3 is 92.

3. Final Answer:

   92 -> Option B

4. Quick Check:

   VLOOKUP returns matching row's column 2 value [OK]

Hint: VLOOKUP returns value from specified column in matched row [OK]

Common Mistakes:

• Returning value from wrong column index
• Getting #N/A if exact match not found
• Confusing lookup value with return value

4. You wrote =VLOOKUP(B2, A2:C10, 4, FALSE) but get an error. What is the problem?

medium

A. The column index 4 is outside the table range columns.

B. The lookup value B2 is missing.

C. The range A2:C10 is not sorted.

D. The FALSE argument is invalid.

Solution

1. Step 1: Check table range columns

   Range A2:C10 has 3 columns only.

2. Step 2: Verify column index argument

   Column index 4 is invalid because it exceeds the number of columns in the range.

3. Final Answer:

   The column index 4 is outside the table range columns. -> Option A

4. Quick Check:

   Column index must be ≤ number of columns in range [OK]

Hint: Column index can't be bigger than table columns count [OK]

Common Mistakes:

• Using column index larger than table columns
• Assuming FALSE is invalid argument
• Thinking lookup value must be in range

5. You have a product list in A2:C6:

Code | Name | Price
P01 | Pen | 1.5
P02 | Book | 7.0
P03 | Bag | 12.5
P04 | Cup | 3.0
P05 | Box | 4.5

How would you write a formula to find the price of product code in E2 exactly, returning "Not found" if code is missing?

hard

A. =IFERROR(VLOOKUP(E2, A2:C6, 3, FALSE), "Not found")

B. =VLOOKUP(E2, A2:C6, 3, TRUE)

C. =VLOOKUP(E2, A2:C6, 2, FALSE)

D. =IF(VLOOKUP(E2, A2:C6, 3, FALSE) = 0, "Not found", VLOOKUP(E2, A2:C6, 3, FALSE))

Solution

1. Step 1: Use VLOOKUP for exact match price

   VLOOKUP with FALSE finds exact product code and returns price from column 3.

2. Step 2: Handle missing code with IFERROR

   Wrap VLOOKUP in IFERROR to return "Not found" if code is missing.

3. Final Answer:

   =IFERROR(VLOOKUP(E2, A2:C6, 3, FALSE), "Not found") -> Option A

4. Quick Check:

   IFERROR catches errors, shows custom message [OK]

Hint: Wrap VLOOKUP in IFERROR to show message if no match [OK]

Common Mistakes:

• Using TRUE instead of FALSE for exact match
• Returning wrong column index value
• Not handling missing lookup values causing errors