XXLFormula AI

Excel

VLOOKUP Function in Excel

VLOOKUP looks for a value in the first column of a range and returns a value in the same row from another column. Still widely used, though XLOOKUP is preferred for new work.

Syntax

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

Parameters

  • lookup_valueRequired

    Value to look for in the first column.

  • table_arrayRequired

    Range that contains the data.

  • col_index_numRequired

    Column number to return (1-based).

  • range_lookupOptional

    FALSE for exact match (recommended), TRUE for approximate.

Examples

Exact match lookup

=VLOOKUP(E2, A2:C100, 3, FALSE)

Finds the value in E2 within column A and returns the 3rd column of the matching row.

Common Errors

  • #N/A

    Cause: Lookup value not present, or hidden trailing spaces / type mismatch.

    Fix: Wrap with IFERROR, or use TRIM/VALUE to normalize the lookup value.

  • #REF!

    Cause: col_index_num is greater than the number of columns in table_array.

    Fix: Verify the column index against the actual range width.

Best Practices

  • Always pass FALSE as range_lookup unless you truly want approximate matches on sorted data.
  • Migrate to XLOOKUP when you can — it avoids column-index fragility.

Frequently Asked Questions

Need a custom version of this formula?

Describe what you need in plain English and get a ready-to-use formula in seconds.

Generate this formula with XLFormula AI