How to use vlookup in excel

Answer

VLOOKUP is used to search for a value in the first column of a table and return a corresponding value from another column in the same row. It is one of the most common lookup functions in Excel.

What is VLOOKUP?

VLOOKUP stands for Vertical Lookup. It searches for a value vertically in the first column of a range and returns a value from a specified column in the same row.

Syntax of VLOOKUP

The syntax of VLOOKUP is:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Explanation of each argument:

1. lookup_value: The value you want to search for.

2. table_array: The range of cells containing the data. The first column of this range is where Excel searches for the lookup value.

3. col_index_num: The column number in the table array from which to retrieve the value. The first column is 1.

4. range_lookup: Optional argument.

   • TRUE (or omitted) → finds an approximate match.

   • FALSE → finds an exact match.

How to Use VLOOKUP

1. Select the cell where you want the result.

2. Enter the VLOOKUP formula:

Example:

You have a table of employee IDs and names:

To find the name of employee ID 102:

=VLOOKUP(102, A2:B4, 2, FALSE)

• 102 → the lookup value.

• A2:B4 → the table range.

• 2 → the column number containing the name.

• FALSE → exact match required.

The result will be Sarah.

Important Tips for VLOOKUP

1. The lookup column must be the first column in your table array.

2. Use FALSE for exact matches to avoid errors.

3. Avoid merged cells; they can cause VLOOKUP to fail.

4. VLOOKUP cannot look to the left. For leftward lookups, use INDEX-MATCH.

5. Large datasets may slow down VLOOKUP; consider XLOOKUP for modern Excel versions.

Example with Approximate Match

When dealing with ranges like grades or commissions:

Formula to find the grade for a score of 75:

=VLOOKUP(75, A2:B6, 2, TRUE)

Result: C, because 75 falls between 70 and 80.

Differences Between Windows and Mac

• Formula syntax and behavior are identical on Windows and Mac.

• On Mac, use Command + Shift + Enter only for array formulas when required, but standard VLOOKUP works normally.

Common Errors

1. #N/A → Lookup value not found or range_lookup mismatch.

2. #REF! → col_index_num is greater than the number of columns in the table.

3. #VALUE! → col_index_num is not numeric.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report