How to get the exact match vlookup

Answer

A VLOOKUP function in Excel searches for a value in the first column of a table array and returns a value in the same row from another column. To get an exact match, you must set the fourth argument of the function to FALSE.

Steps to Use Exact Match VLOOKUP

1. Select the cell where you want the result.

2. Enter the VLOOKUP formula:

   =VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

3. Replace the parameters as follows:

   • lookup_value: The value you want to search for.

   • table_array: The range of cells containing your data.

   • col_index_num: The column number in the table array from which to return the value.

   • FALSE: Ensures Excel searches for an exact match.

Example

Suppose you have the following table in cells A1:B5:

To find the name corresponding to ID 103, use:

=VLOOKUP(103, A1:B5, 2, FALSE)

This returns Steve. Using TRUE instead of FALSE may return incorrect results if the IDs are not sorted.

Notes and Tips

1. The first column of table_array must contain the value you are searching for.

2. FALSE guarantees exact match. TRUE or omitted defaults to approximate match.

3. Exact match is case-insensitive. VLOOKUP treats Mary and MARY as equal.

4. Use IFERROR to handle missing values:

   =IFERROR(VLOOKUP(103, A1:B5, 2, FALSE), "Not Found")

5. On Mac, the formula structure is identical, but Command + Shift + Enter is not required unless using older versions.

Common Issues

1. #N/A error occurs when the value is not found.

2. Leading/trailing spaces can prevent matches. Use TRIM to clean the data.

3. VLOOKUP cannot search to the left. Consider INDEX + MATCH for that scenario.

This method ensures accurate results and prevents mismatched data retrieval.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report