Approximate vs exact match in Excel - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to find the exact match of "Apple" in the range A1:A10.

Excel

=VLOOKUP("Apple", A1:A10, 1, [1])

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

AFALSE

BTRUE

C1

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using TRUE instead of FALSE causes approximate match.

Leaving the last argument empty defaults to approximate match.

Using FALSE in VLOOKUP tells Excel to find an exact match.

2fill in blank

medium

Complete the formula to find the closest match less than or equal to 50 in B1:B10.

Excel

=VLOOKUP(50, B1:B10, 1, [1])

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

ATRUE

BFALSE

C0

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using FALSE causes exact match and may return #N/A if 50 is not found.

Not sorting the lookup range when using TRUE causes wrong results.

Using TRUE enables approximate match, which finds the closest value less than or equal to the lookup value.

3fill in blank

hard

Fix the error in the formula to perform an exact match lookup for "Banana" in C1:D10.

Excel

=VLOOKUP("Banana", C1:D10, 2, [1])

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

ATRUE

B1

CFALSE

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using TRUE causes approximate match and may return incorrect data.

Using 1 instead of FALSE causes approximate match.

FALSE ensures exact match. TRUE or 1 causes approximate match, which may return wrong results or errors.

4fill in blank

hard

Fill both blanks to create a formula that finds the exact match of 100 in E1:F20 and returns the value from the second column.

Excel

=VLOOKUP([1], E1:F20, [2], FALSE)

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

A100

B1

C2

DTRUE

Attempts:

3 left

💡 Hint

Common Mistakes

Using TRUE instead of FALSE for exact match.

Using 1 instead of 2 for the column index.

The lookup value is 100, and the column index to return is 2 for the second column.

5fill in blank

hard

Fill all three blanks to create a formula that looks up "Orange" in G1:H15, returns the value from the second column, and uses exact match.

Excel

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

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

A"Orange"

BG1:H15

C2

DTRUE

Attempts:

3 left

💡 Hint

Common Mistakes

Using TRUE instead of FALSE for exact match.

Using wrong range or column index.

Lookup value is "Orange", range is G1:H15, column index is 2, and FALSE for exact match.

Practice

1. What does the FALSE argument in the VLOOKUP function specify?

easy

A. It sorts the data automatically before searching.

B. It tells Excel to find the closest smaller value.

C. It ignores errors in the lookup value.

D. It tells Excel to find an exact match only.

Solution

1. Step 1: Understand the role of the last argument in VLOOKUP

   The last argument in VLOOKUP is the range_lookup, which controls match type.

2. Step 2: Identify what FALSE means in range_lookup

   When set to FALSE, VLOOKUP looks for an exact match only, returning an error if not found.

3. Final Answer:

   It tells Excel to find an exact match only. -> Option D

4. Quick Check:

   FALSE = exact match [OK]

Hint: FALSE means exact match; TRUE means approximate match [OK]

Common Mistakes:

• Confusing FALSE with approximate match
• Thinking FALSE sorts data automatically
• Assuming FALSE ignores errors

2. Which of the following is the correct syntax for an exact match VLOOKUP formula searching for value in A2 in range B2:D10 and returning the 3rd column?

easy

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

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

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

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

Solution

1. Step 1: Recall exact match syntax for VLOOKUP

   Exact match requires the last argument to be FALSE.

2. Step 2: Check each option

   =VLOOKUP(A2, B2:D10, 3, FALSE) uses FALSE explicitly, which is the standard exact match syntax.

3. Final Answer:

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

4. Quick Check:

   Exact match uses FALSE in VLOOKUP [OK]

Hint: Use FALSE for exact match in VLOOKUP formulas [OK]

Common Mistakes:

• Omitting the last argument defaults to approximate match
• Using TRUE when exact match is needed
• Confusing 0 and FALSE syntax

3. Given this sorted table in cells A1:B5:

A1: Score
A2: 50
A3: 70
A4: 90
A5: 100

What will =VLOOKUP(85, A2:B5, 1, TRUE) return?

medium

A. 70

B. 85

C. 90

D. #N/A

Solution

1. Step 1: Understand approximate match behavior

   With TRUE, VLOOKUP finds the largest value less than or equal to lookup value (85) in sorted data.

2. Step 2: Find closest smaller or equal value to 85

   Values are 50, 70, 90, 100. The largest value ≤ 85 is 70.

3. Final Answer:

   70 -> Option A

4. Quick Check:

   Approximate match returns closest smaller value [OK]

Hint: TRUE finds closest smaller value in sorted data [OK]

Common Mistakes:

• Expecting exact match with TRUE
• Choosing 90 because it's closest larger value
• Getting #N/A error for approximate match

4. You used =VLOOKUP(75, A2:B5, 2, TRUE) but got wrong results. The data in A2:A5 is not sorted. What is the best fix?

medium

A. Change the column index to 1.

B. Change TRUE to FALSE to force exact match.

C. Sort the data in ascending order before using TRUE.

D. Use INDEX instead of VLOOKUP.

Solution

1. Step 1: Identify problem with unsorted data and approximate match

   Approximate match (TRUE) requires data sorted ascending to work correctly.

2. Step 2: Fix by sorting data ascending

   Sorting the lookup column ascending ensures approximate match finds correct closest smaller value.

3. Final Answer:

   Sort the data in ascending order before using TRUE. -> Option C

4. Quick Check:

   Approximate match needs sorted data [OK]

Hint: Sort data ascending before using TRUE in VLOOKUP [OK]

Common Mistakes:

• Switching to FALSE without needing exact match
• Changing column index without reason
• Replacing VLOOKUP with INDEX unnecessarily

5. You have a price list sorted by quantity:

Qty: 1, 10, 20, 50
Price: 100, 90, 80, 70

Which formula correctly finds the price for an order of 15 units using approximate match?

hard

A. =VLOOKUP(15, A2:B5, 2, TRUE)

B. =VLOOKUP(15, A2:B5, 2, FALSE)

C. =VLOOKUP(15, A2:B5, 1)

D. =VLOOKUP(15, A2:B5, 2, 0)

Solution

1. Step 1: Understand the need for approximate match in price tiers

   For quantity 15, price depends on closest smaller quantity tier (10), so approximate match is needed.

2. Step 2: Choose formula with TRUE for approximate match

   =VLOOKUP(15, A2:B5, 2, TRUE) uses TRUE, enabling approximate match to find price for 10 units tier.

3. Final Answer:

   =VLOOKUP(15, A2:B5, 2, TRUE) -> Option A

4. Quick Check:

   Approximate match finds correct tier price [OK]

Hint: Use TRUE for tiered pricing with sorted quantity [OK]

Common Mistakes:

• Using FALSE causes #N/A if exact quantity missing
• Omitting last argument defaults to TRUE but unclear
• Using 0 instead of TRUE for approximate match