VLOOKUP function in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to look up a value in the first column of a table and return the value from the second column.

Excel

=VLOOKUP(A2, B2:D10, [1], FALSE)

Drag options to blanks, or click blank then click option'

A2

B3

C1

D4

Attempts:

3 left

💡 Hint

Common Mistakes

Using 1 as the column index returns the lookup value itself.

Using a column index greater than the number of columns in the table causes an error.

The third argument in VLOOKUP is the column index number from which to return the value. Since we want the second column, we use 2.

2fill in blank

medium

Complete the formula to perform an exact match lookup for the value in cell A5.

Excel

=VLOOKUP(A5, A1:C20, 3, [1])

Drag options to blanks, or click blank then click option'

AFALSE

BTRUE

C0

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using TRUE instead of FALSE causes approximate matches.

Omitting the fourth argument defaults to TRUE, which may not be desired.

The fourth argument in VLOOKUP specifies whether to find an approximate match (TRUE) or exact match (FALSE). For exact match, use FALSE.

3fill in blank

hard

Fix the error in the formula to correctly look up the value in cell B3 and return the value from the fourth column.

Excel

=VLOOKUP([1], A1:D15, 4, FALSE)

Drag options to blanks, or click blank then click option'

AD3

BB3

CC3

DA3

Attempts:

3 left

💡 Hint

Common Mistakes

Using a cell reference outside the lookup value.

Using a column reference instead of a cell reference.

The lookup value should be the cell B3 as stated. Using B3 ensures the formula looks up the correct value.

4fill in blank

hard

Fill both blanks to create a VLOOKUP formula that looks up the value in cell C2, searches the range A2:E20, and returns the value from the fifth column with an exact match.

Excel

=VLOOKUP([1], [2], 5, FALSE)

Drag options to blanks, or click blank then click option'

AC2

BB2

CA2:E20

DA1:D20

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong cell for lookup value.

Using a range that does not include the lookup value column.

The lookup value is cell C2, and the table array is the range A2:E20. The formula returns the value from the fifth column with exact match.

5fill in blank

hard

Fill all three blanks to create a VLOOKUP formula that looks up the value in cell D4, searches the range B1:F30, and returns the value from the third column with an exact match.

Excel

=VLOOKUP([1], [2], [3], FALSE)

Drag options to blanks, or click blank then click option'

AD4

BB1:F30

C3

DE4

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong column index.

Using a lookup value outside the table array.

The lookup value is D4, the table array is B1:F30, and the column index is 3 to return the third column's value with exact match.

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