If you’re working with spreadsheets in Excel or Google Sheets, VLOOKUP is one of the most useful functions you’ll ever learn. It helps you search for a value in one column and return a matching value from another column, making data analysis faster and easier.

What is VLOOKUP?

VLOOKUP stands for “Vertical Lookup”. It’s used to find data in a vertical table—like a list of products, employees, or student records—based on a unique identifier.

Formula Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

1. lookup_value: What you want to search for

2. table_array: The table containing the data

3. col_index_num: The column number with the data you want to return

4. range_lookup: TRUE for approximate match, FALSE for exact match

Example: VLOOKUP in Excel

Scenario: You have a table of products and prices. You want to find the price of a specific product.

Formula: =VLOOKUP("Banana", A2:B4, 2, FALSE)

Result: 30

This means Excel searched for “Banana” in column A and returned the value from column B.

How to Use VLOOKUP Between Two Sheets

1. Open both sheets.

2. Use the sheet name in the formula:

This will search the second sheet for “Mango” and return its associated value.

How to Use VLOOKUP in Google Sheets

Google Sheets uses the same formula as Excel.

Example: =VLOOKUP("Apple", A2:B10, 2, FALSE)

Common VLOOKUP Errors and Fixes

• #N/A: The value wasn’t found – check for typos.

• #REF!: The column number is incorrect or too high.

• Wrong results: You may have used TRUE instead of FALSE.

When to Use XLOOKUP Instead

XLOOKUP is the more advanced, flexible version of VLOOKUP available in Excel 365.

Example: =XLOOKUP("Banana", A2:A4, B2:B4)

Unlike VLOOKUP, XLOOKUP can search both vertically and horizontally.

Learn more on the official Microsoft site: 🔗 Microsoft Excel XLOOKUP Function

Final Tips

• Always use FALSE for exact matches unless you understand approximate lookups.

• Make sure your lookup column is sorted if you’re using TRUE.

• Practice with simple tables first, then apply VLOOKUP in larger datasets.