Short Answer:

In VLOOKUP, an exact match returns a value only when the lookup value matches exactly with a value in the first column of the table. For example, =VLOOKUP(102, A2:C10, 3, FALSE) will return a result only if 102 exists in the table; otherwise, it shows an error.

An approximate match returns the closest value that is less than or equal to the lookup value. It is used with sorted data and can fill in results even if there is no exact match. For example, =VLOOKUP(102, A2:C10, 3, TRUE) finds the nearest lower value to 102.

Detailed Explanation:

Difference between Approximate and Exact Match

The VLOOKUP function in Excel can work in two ways depending on the last argument called range_lookup. This argument determines whether the function searches for an exact value or allows an approximate match.

1. Exact Match (FALSE)

• Exact match requires that the lookup value matches a value in the first column of the table exactly.
• If the value is not found, Excel returns #N/A error.
• It is used when you need precise results, such as student IDs, employee codes, or product SKUs.
• Example: =VLOOKUP(102, A2:C10, 3, FALSE)
  • Searches for 102 in column A.
  • Returns the value from column 3 only if 102 exists.

1. Approximate Match (TRUE)

• Approximate match returns the closest value that is less than or equal to the lookup value.
• The data in the first column must be sorted in ascending order for accurate results.
• It is used when exact values may not exist, such as grading scales or price ranges.
• Example: =VLOOKUP(102, A2:C10, 3, TRUE)
  • Searches for the largest value ≤ 102 in column A.
  • Returns the corresponding value from column 3.
• If the exact value is found, it returns that value; otherwise, it gives the nearest lower match.

Key Differences

1. Match Type: Exact match looks for a perfect match; approximate match finds the nearest lower value.
2. Data Requirement: Exact match works with unsorted data; approximate match requires sorted data in ascending order.
3. Result When Not Found: Exact match returns an error if not found; approximate match returns the nearest lower value.
4. Use Cases: Exact match is for codes, IDs, or text; approximate match is for ranges, thresholds, or intervals.

Practical Examples

1. Exact Match: Retrieve a student’s score by ID:
   =VLOOKUP(205, A2:C50, 3, FALSE) – Only returns the score if ID 205 exists.
2. Approximate Match: Determine a grade based on score ranges:
   =VLOOKUP(87, A2:B10, 2, TRUE) – Finds the grade for the closest score ≤ 87.
3. Price Table: Determine discount for purchase amount:
   =VLOOKUP(450, A2:B6, 2, TRUE) – Returns discount for nearest lower purchase amount.

Exact and approximate matches give flexibility in Excel. Exact match ensures precision, whereas approximate match allows using ranges and graded results. Knowing when to use each type ensures accurate and efficient data retrieval.

Conclusion:

The difference between approximate and exact match in VLOOKUP lies in how Excel searches for the lookup value. Exact match finds only the precise value, while approximate match finds the nearest lower value in sorted data. Choosing the right type depends on whether you need precise results or are working with ranges and thresholds.