VLOOKUP function in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the VLOOKUP function do in Google Sheets?

VLOOKUP searches for a value in the first column of a range and returns a value in the same row from another column you specify.

beginner

What are the four arguments of the VLOOKUP function?

The four arguments are:
1. search_key: The value to find.
2. range: The table to search in.
3. index: The column number in the range to return the value from.
4. is_sorted: TRUE for approximate match, FALSE for exact match.

beginner

What happens if you set the is_sorted argument to FALSE in VLOOKUP?

VLOOKUP looks for an exact match of the search_key. If it doesn't find one, it returns an error (#N/A).

beginner

Why should the search_key be in the first column of the range in VLOOKUP?

Because VLOOKUP always searches for the search_key in the first column of the range. If it's not there, it won't find the value.

beginner

How do you use VLOOKUP to find a price of an item named "Apple" in a list where names are in column A and prices in column B?

Use =VLOOKUP("Apple", A:B, 2, FALSE). It looks for "Apple" in column A and returns the price from column B.

In VLOOKUP, what does the 'index' argument specify?

A The column number in the range to return the value from

B The row number to search

C The value to find

D Whether to sort the data

The 'index' tells VLOOKUP which column's value to return from the range.

What will VLOOKUP return if it cannot find an exact match and is_sorted is FALSE?

A Zero

B The closest match

C The first value in the range

D An error (#N/A)

With is_sorted FALSE, VLOOKUP returns #N/A if no exact match is found.

Where does VLOOKUP look for the search_key in the range?

A In the first column

B In the middle column

C In the last column

D In any column

VLOOKUP always searches for the search_key in the first column of the range.

Which formula correctly finds the price of "Banana" in columns A (names) and B (prices)?

A =VLOOKUP("Banana", B:A, 1, TRUE)

B =VLOOKUP("Banana", A:B, 1, FALSE)

C =VLOOKUP("Banana", A:B, 2, FALSE)

D =VLOOKUP("Banana", A:B, 3, FALSE)

Option C searches for "Banana" in column A and returns the price from column 2 (B).

What does setting is_sorted to TRUE do in VLOOKUP?

A Requires exact match only

B Allows approximate match and requires sorted data

C Returns an error if no match

D Searches backwards

TRUE allows approximate match but the first column must be sorted ascending.

Explain how to use VLOOKUP to find information in a table. Include what each argument means.

Think about looking up a friend's phone number in a contact list.

Describe a common mistake when using VLOOKUP and how to fix it.

Imagine looking for a name in the wrong column.

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