VLOOKUP is one of the most powerful and most searched Excel formulas — and for good reason. Once you understand it, you can find any value in a dataset in seconds. This guide walks you through everything from basic syntax to advanced use cases.
What Does VLOOKUP Do?
VLOOKUP stands for Vertical Lookup. It searches the first column of a table for a value you specify, then returns a value from a different column in the same row.
Think of it as saying: "Find 'Apple' in column A, then tell me what's in column C of that same row."
VLOOKUP Syntax
- lookup_value — the value you're searching for (e.g., "Apple" or A2)
- table_array — the range containing your data (e.g., A2:C100)
- col_index_num — which column to return (1 = first column, 2 = second, etc.)
- range_lookup — use FALSE for exact match (almost always what you want)
Basic Example
You have a product list: Column A has product names, Column B has prices. To find the price of "Laptop":
This finds "Laptop" in column A and returns the value from column B (the 2nd column).
The Most Important Rule: Always Use FALSE
⚠️ Warning: Without FALSE as the last argument, Excel performs an approximate match. This requires your data to be sorted and often gives completely wrong results on unsorted data. Always use FALSE for exact matching.
Common Mistakes and Fixes
Mistake 1: Column Index Out of Range
If your table is A2:C100 (3 columns) and you use col_index 4, you'll get a #REF! error. Count your columns carefully — column index counts from the START of your table_array, not from column A of the sheet.
Mistake 2: Not Using IFERROR
When VLOOKUP can't find a match, it returns #N/A. Wrap it with IFERROR to handle this gracefully:
Mistake 3: Extra Spaces in Data
"Apple " and "Apple" look identical but won't match. Use TRIM() to clean your data before running VLOOKUP.
VLOOKUP vs XLOOKUP
If you have Excel 2021 or Microsoft 365, use XLOOKUP instead — it's more powerful and simpler:
XLOOKUP can search in any direction, doesn't need a column index number, and has built-in error handling.
💡 Pro tip: Use absolute references ($A$2:$C$100) when copying your VLOOKUP formula across multiple cells — this locks the table range so it doesn't shift as you copy.
Google Sheets vs Excel
VLOOKUP works identically in Google Sheets. Same syntax, same arguments, same rules. The only difference: in Sheets, some newer functions like XLOOKUP may have slightly different availability.
Generate VLOOKUP Formulas Instantly
Describe what you want to look up in plain English. FormulaZa writes the exact formula for you — free, no signup required.
Try FormulaZa Free →