VLOOKUP function in Google Sheets - 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 product price by entering its name.

When you need to get a student's grade by looking up their ID number.

When you want to match employee names with their phone numbers from a list.

When you have a list of cities and want to find their population from another table.

When you want to combine data from two sheets based on a common key.

Steps

Step 1: Click

- the cell where you want the result

The cell is selected and ready for input

Step 2: Type

- the selected cell

You start entering the formula

💡 Start with an equal sign (=) to tell Sheets you are entering a formula

Step 3: Enter

- the formula bar or cell

Formula looks like =VLOOKUP(search_key, range, index, [is_sorted])

💡 For example: =VLOOKUP(A2, B2:D10, 3, FALSE)

Step 4: Press

- Enter key

The cell shows the matching value from the table

Step 5: Check

- the formula and data

You see the correct value or an error if no match is found

Before vs After

Before

Cell C2 is empty; you have a table in B2:D10 with product names, IDs, and prices

After

Cell C2 shows the price of the product named in A2 by looking it up in the table

Settings Reference

search_key

📍 first argument in VLOOKUP formula

The value you want to find in the first column of the range

Default: none

range

📍 second argument in VLOOKUP formula

The table where you look for the search_key and get the result

Default: none

index

📍 third argument in VLOOKUP formula

The column number in the range to return the value from

Default: none

is_sorted

📍 fourth optional argument in VLOOKUP formula

TRUE finds approximate match; FALSE finds exact match

Default: TRUE

Common Mistakes

Using TRUE or leaving is_sorted blank when you want an exact match

This can return wrong or unexpected results if the data is not sorted

Always use FALSE for exact matches to get correct results

Setting index to 1 or less

Index 1 returns the search column itself, not the value you want

Set index to 2 or higher to get data from columns to the right

Not fixing the range with $ signs when copying the formula

The range shifts and causes wrong lookups

Use absolute references like $B$2:$D$10 to keep the range fixed

Summary

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

Use FALSE for exact matches to avoid errors.

Fix the lookup range with $ signs when copying formulas to other cells.

Practice

1.

What does the VLOOKUP function do in Google Sheets?

easy

A. It looks for a value in the first column of a range and returns a value from another column in the same row.

B. It adds all numbers in a column.

C. It sorts data alphabetically.

D. It counts the number of cells with numbers.

Solution

1. Step 1: Understand the purpose of VLOOKUP

   VLOOKUP searches for a value in the first column of a given range.

2. Step 2: Identify what it returns

   It returns a value from another column in the same row where the match is found.

3. Final Answer:

   It looks for a value in the first column of a range and returns a value from another column in the same row. -> Option A

4. Quick Check:

   VLOOKUP finds and returns matching row data [OK]

Hint: Remember: VLOOKUP searches first column, returns from another [OK]

Common Mistakes:

• Thinking VLOOKUP sums or counts data
• Confusing VLOOKUP with sorting functions
• Assuming it searches all columns, not just the first

2.

Which of the following is the correct syntax for an exact match VLOOKUP in Google Sheets?

=VLOOKUP(lookup_value, range, col_index, FALSE)

easy

A. =VLOOKUP(range, lookup_value, col_index, FALSE)

B. =VLOOKUP(lookup_value, range, col_index, TRUE)

C. =VLOOKUP(lookup_value, range, col_index, FALSE)

D. =VLOOKUP(lookup_value, col_index, range, FALSE)

Solution

1. Step 1: Recall VLOOKUP syntax order

   The correct order is lookup_value, range, column index, and match type.

2. Step 2: Identify exact match parameter

   FALSE is used for exact match, TRUE for approximate.

3. Final Answer:

   =VLOOKUP(lookup_value, range, col_index, FALSE) -> Option C

4. Quick Check:

   Exact match uses FALSE as last argument [OK]

Hint: Exact match always ends with FALSE in VLOOKUP [OK]

Common Mistakes:

• Swapping lookup_value and range
• Using TRUE instead of FALSE for exact match
• Mixing order of parameters

3.

Given this table in cells A1:B4:

A1: Product  B1: Price
A2: Apple    B2: 1.5
A3: Banana   B3: 0.75
A4: Cherry   B4: 2.0

What will =VLOOKUP("Banana", A1:B4, 2, FALSE) return?

medium

A. Banana

B. 0.75

C. 1.5

D. 2.0

Solution

1. Step 1: Locate "Banana" in first column

   Banana is found in cell A3.

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

   The second column in row 3 is B3, which is 0.75.

3. Final Answer:

   0.75 -> Option B

4. Quick Check:

   VLOOKUP returns price 0.75 for Banana [OK]

Hint: Find lookup in first column, return from given column index [OK]

Common Mistakes:

• Returning the lookup value instead of the result column
• Using approximate match causing wrong results
• Confusing column index numbers

4.

What is wrong with this formula if you want an exact match lookup?

=VLOOKUP("Orange", A2:B5, 2)

medium

A. Using quotes around Orange

B. Missing the column index parameter

C. Missing the range parameter

D. Missing the FALSE parameter for exact match

Solution

1. Step 1: Check parameters in formula

   The formula has lookup_value, range, and column index but no match type.

2. Step 2: Identify default match behavior

   Without FALSE, VLOOKUP uses approximate match (TRUE) by default, which may give wrong results.

3. Final Answer:

   Missing the FALSE parameter for exact match -> Option D

4. Quick Check:

   Exact match requires FALSE as last argument [OK]

Hint: Always add FALSE for exact match in VLOOKUP [OK]

Common Mistakes:

• Assuming exact match is default
• Forgetting to add FALSE parameter
• Thinking quotes cause error

5.

You have a product list in A2:C6 with columns: Product, Category, Price.

You want to find the category of "Mango" using VLOOKUP. Which formula correctly returns the category?

hard

A. =VLOOKUP("Mango", A2:C6, 2, FALSE)

B. =VLOOKUP("Mango", B2:C6, 2, FALSE)

C. =VLOOKUP("Mango", A2:C6, 1, FALSE)

D. =VLOOKUP("Mango", A2:C6, 3, FALSE)

Solution

1. Step 1: Identify the column index for Category

   Columns are: 1=Product, 2=Category, 3=Price. Category is column 2.

2. Step 2: Check the range and lookup value

   Range A2:C6 includes Product in first column, so lookup works correctly.

3. Final Answer:

   =VLOOKUP("Mango", A2:C6, 2, FALSE) -> Option A

4. Quick Check:

   Category is column 2, so col_index=2 [OK]

Hint: Match column index to desired return column in VLOOKUP [OK]

Common Mistakes:

• Using wrong column index for category
• Using a range that does not start with lookup column
• Confusing column numbers with letters