VLOOKUP is one of the most powerful and commonly used functions in Excel, LibreOffice Calc, and Google Sheets. It allows you to search for a value in the first column of a table and return a value from another column in the same row.

What is VLOOKUP?

VLOOKUP stands for "Vertical Lookup." It searches for a value vertically (down a column) and returns a corresponding value from another column in the same row.

VLOOKUP Syntax

The basic syntax for VLOOKUP is:

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

Parameters Explained:

• lookup_value: The value you want to search for (must be in the first column of your table)
• table_array: The range of cells containing the data table
• col_index_num: The column number from which to return the value (1 = first column, 2 = second column, etc.)
• range_lookup: Optional. TRUE (or 1) for approximate match, FALSE (or 0) for exact match

VLOOKUP Examples

Example 1: Basic VLOOKUP

Let's say you have a product list with prices, and you want to find the price of a specific product:

=VLOOKUP("Product A", A2:B10, 2, FALSE)

This searches for "Product A" in column A (A2:A10) and returns the corresponding price from column B.

Example 2: VLOOKUP with Cell Reference

Instead of hardcoding the lookup value, use a cell reference:

=VLOOKUP(D2, A2:B10, 2, FALSE)

This looks up the value in cell D2 and finds its match in the table.

Example 3: VLOOKUP for Employee Data

Find an employee's department based on their ID:

=VLOOKUP(101, EmployeeTable, 3, FALSE)

This searches for employee ID 101 and returns the department (column 3).

Common VLOOKUP Mistakes

1. Forgetting FALSE for Exact Match

Always use FALSE or 0 for exact matches. If omitted, VLOOKUP defaults to approximate match, which can return incorrect results.

2. Lookup Value Not in First Column

VLOOKUP only searches the first column of your table. If your lookup value is in a different column, use INDEX MATCH instead.

3. Column Index Number Error

Remember: column index starts at 1, not 0. The first column is 1, second is 2, and so on.

4. Not Using Absolute References

When copying VLOOKUP formulas, use absolute references ($) for the table array:

=VLOOKUP(D2, $A$2:$B$10, 2, FALSE)

VLOOKUP in Different Spreadsheet Applications

Microsoft Excel

VLOOKUP works identically in all versions of Excel. The syntax is the same across Excel 2016, Excel 2019, Excel 365, and Excel Online.

LibreOffice Calc

LibreOffice Calc uses the same VLOOKUP syntax as Excel. However, Calc uses semicolons (;) instead of commas (,) as separators in some locales:

=VLOOKUP(D2; A2:B10; 2; 0)

Google Sheets

Google Sheets VLOOKUP works exactly like Excel. Use commas as separators:

=VLOOKUP(D2, A2:B10, 2, FALSE)

When to Use INDEX MATCH Instead

While VLOOKUP is powerful, INDEX MATCH is often preferred because it:

• Can search left (VLOOKUP only searches right)
• Is more flexible with column positions
• Performs better with large datasets
• Doesn't break when columns are inserted

Generate VLOOKUP Formulas with AI

Struggling to write the perfect VLOOKUP formula? Use ExcelFormula Pro to generate VLOOKUP formulas instantly. Just describe what you need in plain English, and our AI creates the formula for you!

Conclusion

VLOOKUP is an essential function for anyone working with spreadsheets. Whether you're using Microsoft Excel, LibreOffice Calc, or Google Sheets, mastering VLOOKUP will save you hours of manual work. Remember to use FALSE for exact matches, and consider INDEX MATCH for more complex scenarios.

Related Articles:

• INDEX MATCH vs VLOOKUP: Which Should You Use?
• IF Formula: 10 Practical Examples
• SUMIF Formula: Sum with Conditions