VLOOKUP
The classic vertical lookup. Look up a value in the first column of a table and return a value from another column in the same row. Third most-used Excel function on earth — and the one you'll most often see in someone else's workbook.
Searches for a value in the leftmost column of a range and returns the value from a specified column in the same row.
What VLOOKUP does
VLOOKUP stands for Vertical Lookup. It's the classic way to look up a value in a table and return related information. Imagine you have a product catalog with product IDs in column A and prices in column B. Given a product ID, VLOOKUP finds the row containing that ID and returns the price. That's the fundamental workflow, and it applies to thousands of business scenarios — matching customer names to accounts, employee IDs to salaries, part numbers to inventory levels.
VLOOKUP was introduced in the earliest versions of Excel and has become the most widely known lookup function in the world. In 2020, Microsoft released XLOOKUP as its official replacement — cleaner syntax, more flexible, safer defaults. But VLOOKUP isn't going anywhere: billions of existing workbooks depend on it, and every finance/analyst professional still needs to read and write it fluently.
Syntax breakdown
VLOOKUP takes four arguments. The first three are required; the fourth is optional but you should always set it explicitly.
The value you want to find. Can be a number, text, date, cell reference, or expression. If it's text, wrap in quotes: "Widget-42". If it's a cell reference: A2.
The range containing the lookup table. Critical: the value you're searching for must be in the FIRST column of this range. VLOOKUP always searches left-to-right — it cannot look up a value in column B and return column A.
Which column of the table_array contains the value you want returned. Column 1 is the leftmost (same as the lookup column), column 2 is the next one over, and so on. If the range is A2:D100, column 2 is B, column 3 is C, column 4 is D.
FALSE or 0 forces an exact match — the value must match precisely or #N/A is returned. TRUE or 1 (the default) allows approximate match — for this to work correctly, the lookup column MUST be sorted ascending. Almost always use FALSE for business lookups. Leaving it blank is a common source of silent wrong-answer bugs.
How VLOOKUP works step by step
- Excel takes your
lookup_valueand searches down the FIRST column of thetable_array, top to bottom. - When it finds a match, it stops at that row.
- It then moves right, counting columns, until it reaches column number
col_index_num. - It returns whatever value is in that cell.
- If no match is found, VLOOKUP returns
#N/A.
Understanding this movement — down first, then right — is the key to understanding VLOOKUP's biggest limitation: it can never look "left" of the search column.
5 real-world examples
Find a product's price by ID
You have a product catalog in A2:B100 with product IDs in A and prices in B. In cell E2 you've typed a product ID like P-1042. You want F2 to show that product's price.
What happens: Excel searches for "P-1042" in column A. When found, it returns the value in column 2 of the range (which is column B). FALSE ensures exact match.
Approximate match for a scaled table
You have a tax bracket table where income levels map to tax rates. Row structure: 0 → 10%, 50000 → 22%, 100000 → 32%, 200000 → 37%. Given an income of $75,000, you want the applicable tax rate.
Critical: approximate match requires the lookup column sorted ascending. If unsorted, VLOOKUP silently returns wrong values with no error.
Wrap in IFERROR to prevent #N/A in reports
Lookups fail. Customer not found. Product doesn't exist. Instead of showing ugly #N/A, return a graceful message.
This pattern should wrap every VLOOKUP in a production workbook. Read our IFERROR guide for more.
Two-way lookup — flexible column selection
You have monthly sales data with product names in column A and month names as headers (Jan, Feb, Mar in B1:M1). You want to look up a product's sales for a specific month.
Instead of hardcoding 4 as the column index, MATCH finds where "Mar" appears in the header row. This makes the formula adapt automatically if columns are rearranged.
Partial match with asterisk
You have a customer database with full names like "Ahmed Khan Enterprises" in column A. Given only "Khan" in E2, you want to find the first matching row.
The * wildcards mean "any characters before or after". Wildcards only work with FALSE (exact match) — Excel treats the wildcarded string as the "exact" pattern to find.
Common errors and how to fix them
Trailing or leading spaces in the data
Your lookup value is "CUST-023" but the table has "CUST-023 " with a trailing space. To your eyes they look identical. Excel treats them as different values.
Numbers stored as text vs actual numbers
Common with CSV imports. Your lookup value is the number 42 but the table has the text "42" (or vice versa). VLOOKUP won't match these across types.
Approximate match on unsorted data
You left range_lookup blank (defaults to TRUE), but your lookup column isn't sorted ascending. VLOOKUP silently returns wrong values, or #N/A when it thinks the value doesn't exist.
Lookup value not in the first column
You want to look up a customer name (column B) and return their ID (column A). VLOOKUP can't do this — it only searches the leftmost column of the range.
col_index_num larger than the table has columns
You wrote =VLOOKUP(E2, A2:C100, 5, FALSE) but the range only has 3 columns (A, B, C). Column 5 doesn't exist.
col_index_num less than 1
You wrote =VLOOKUP(E2, A2:C100, 0, FALSE) — column 0 doesn't exist. Column numbering starts at 1.
For a complete list of lookup errors, see our Lookup Errors guide.
VLOOKUP vs XLOOKUP vs INDEX/MATCH
These three approaches solve the same fundamental problem. Which should you use?
Version compatibility
Related functions
📥 Download the practice workbook
All 5 examples above in a working .xlsx file with sample data. Open in Excel or Google Sheets and try modifying the formulas.
Frequently asked questions
Should I stop using VLOOKUP in 2026?
Only in new work where you control the workbook and you're on Excel 365 or 2021+. In those cases, XLOOKUP is a better default. But VLOOKUP still works everywhere, isn't deprecated, and every workbook you inherit will use it. Learn both.
Why is my VLOOKUP returning #N/A when I can clearly see the value?
99% of the time it's an invisible data mismatch. Test with =EXACT(lookup_value, target_cell). If FALSE, the values differ — trailing spaces, non-breaking spaces from web pastes, numbers stored as text, or casing issues. Wrap both in TRIM() to test.
Can VLOOKUP search left?
No. VLOOKUP always searches the leftmost column of its range and returns something to the right. For left lookups, use INDEX/MATCH or XLOOKUP. This is VLOOKUP's biggest limitation.
Is INDEX/MATCH really faster than VLOOKUP?
On modern Excel with modern computers, the speed difference is negligible for most workbooks. Where INDEX/MATCH wins is flexibility — bidirectional lookups, two-way lookups, and complex scenarios VLOOKUP can't do. The speed argument was more valid on old hardware.
How do I VLOOKUP with multiple criteria?
Two approaches. Traditional: create a helper column concatenating your criteria (e.g., =A2&"|"&B2) and VLOOKUP against that. Modern: use FILTER with multiplied conditions, or XLOOKUP with concatenated arguments.
Why does VLOOKUP with approximate match return wrong answers silently?
Because approximate match assumes your lookup column is sorted ascending. If it isn't, VLOOKUP returns whatever value happens to be at the "would-be" insertion point — often completely wrong. Always add FALSE explicitly unless you specifically need bracket-lookup behavior on sorted data.
What's the maximum table_array size for VLOOKUP?
Excel's total limit is 1,048,576 rows × 16,384 columns. VLOOKUP works across the entire sheet if needed. For huge datasets (100K+ rows), performance may slow — consider Excel Tables, Power Query, or Power Pivot for cleaner alternatives.
Never write VLOOKUP by hand again.
Describe what you want — "look up customer name from the orders table" — and the Add-in writes the exact VLOOKUP or XLOOKUP formula for you.
Get the Add-in →