Mastering VLOOKUP: The Ultimate Guide

The VLOOKUP (Vertical Lookup) function is the backbone of data merging in Excel and Google Sheets. Whether you are reconciling invoices or searching for employee IDs, understanding how to structure this formula is essential for any spreadsheet user.

Common Pitfalls to Avoid

  • The Left-to-Right Rule: Remember that VLOOKUP can only look for a value in the leftmost column of your range. If your lookup value is to the right of your result, VLOOKUP won't work.
  • Approximate vs. Exact Match: Always use FALSE or 0 as the last argument if you need an exact match (like an ID or Name). Otherwise, you might get the nearest smaller value instead.
  • Static Column Index: Hardcoding a column number (e.g., 3) makes your formula fragile. If you insert a new column, the index won't update, leading to broken data.

Pro Tip: If your lookup value is not in the first column, consider using INDEX & MATCH or the more modern XLOOKUP Generator which removes these limitations entirely.

Common Errors & Fixes

  • VLOOKUP returns #N/A

    Causes:
    • Lookup value not in the first column of table_array.
    • Data type mismatch (number vs text, e.g. 123 vs "123").
    • Extra spaces or different formatting in lookup value or table.
    Fixes:
    • Ensure the column you search is the leftmost in table_array.
    • Use TRIM and VALUE or TEXT to align types; check for leading zeros.
    • Use TRIM on both sides or normalize with VALUE/TEXT.

  • Wrong column returned

    Causes:
    • col_index_num is 1-based; counting from 1, not 0.
    • Inserted columns shifted the return column; index not updated.
    Fixes:
    • Count columns from the first column of table_array (1 = first column).
    • Use INDEX/MATCH or XLOOKUP to avoid column index breakage.

Frequently Asked Questions

Why is VLOOKUP returning #N/A?

This usually means the lookup value does not exist in the first column of your table array, or there is a mismatch in data types (e.g., number vs text stored as number). Check for hidden spaces, leading zeros, or use TRIM and VALUE to normalize data.

Can VLOOKUP look to the left?

No, VLOOKUP can only look to the right. Use XLOOKUP or INDEX/MATCH to look to the left or in any column.

What is the difference between VLOOKUP and XLOOKUP?

VLOOKUP only looks right and requires a column index number. XLOOKUP looks in any direction, uses separate lookup and return arrays, and supports built-in if-not-found and default values.

How to use VLOOKUP with multiple criteria?

Add a helper column that concatenates the criteria columns, then use VLOOKUP on that column. In Excel 365 you can also use XLOOKUP with multiple conditions.

Why is VLOOKUP not working?

Common causes: data type mismatch (number vs text), extra spaces (use TRIM), wrong column index, or range_lookup set to TRUE when you need exact match. Use FALSE for exact match.

Is VLOOKUP case sensitive?

No, VLOOKUP is not case sensitive. To do a case-sensitive lookup, use INDEX with MATCH and EXACT, or XLOOKUP with EXACT.

How to fix VLOOKUP #REF error?

#REF usually means the column index number is greater than the columns in your range, or the range was deleted. Check that col_index_num does not exceed the number of columns in table_array.

Should I use VLOOKUP or INDEX MATCH?

Use INDEX MATCH when you need to look left, when columns might be inserted, or for clearer formulas. Use VLOOKUP for simple right-only lookups where the table rarely changes.

Related Formulas

INDEX/MATCHXLOOKUPIFERRORSUMIFCOUNTIF

Want to become an Excel Pro?

Stop searching for formulas. Master Excel in 30 days with this top-rated course.

Learn More