VLOOKUP.
D2What is VLOOKUP?
VLOOKUP stands for Vertical Lookup. It is one of the most widely used spreadsheet functions in both Microsoft Excel and Google Sheets. VLOOKUP searches for a value in the first column of a table and returns a value from another column in the same row.
Think of it like looking up a contact in your phone. You search by name (the lookup value), and the phone returns the associated phone number (the value from another column). VLOOKUP works the same way — you provide a value to search for, tell it which table to look in, and specify which column holds the data you want back.
VLOOKUP is available in Microsoft Excel, Google Sheets, and Apple Numbers, making it one of the most portable formulas across spreadsheet platforms. Whether you are pulling product prices, matching employee records, or grading student scores, VLOOKUP is often the first formula people reach for.
VLOOKUP Syntax
The VLOOKUP function takes four arguments:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])- lookup_value — The value you want to find. This can be a cell reference (e.g.,
E1), a number, or a text string in quotes. - table_array — The range of cells that contains the data. VLOOKUP always searches the first column of this range for the lookup value.
- col_index_num — The column number in the table from which to return a value. Column 1 is the first column in the table_array, column 2 is the second, and so on.
- [range_lookup] — Optional. Use
FALSEfor an exact match (most common) orTRUEfor an approximate match. If omitted, it defaults toTRUE.
VLOOKUP Examples
Below are practical, real-world examples showing how VLOOKUP solves common spreadsheet tasks. Each example includes the natural language description you could type into FormulaAI, followed by the generated formula.
1. Look Up a Product Price by ID
You have a product catalog in columns A through C (Product ID, Product Name, Price). You want to find the price for the product ID entered in cell E1.
Description: "Find the price for the product ID in E1 from the catalog in A:C"
=VLOOKUP(E1, A:C, 3, FALSE)This searches column A for the value in E1, then returns the corresponding value from column 3 (Price). The FALSE argument ensures an exact match — you want the exact product ID, not an approximate one.
2. Find an Employee's Department
Your HR spreadsheet has Employee ID in column A, Employee Name in column B, and Department in column C. You want to look up which department employee "E1042" belongs to.
Description: "Look up department for employee ID E1042 in the employee table A2:C200"
=VLOOKUP("E1042", A2:C200, 3, FALSE)Here the lookup value is a text string "E1042" enclosed in quotes. The formula searches column A of the range A2:C200 and returns the value from column 3 (Department).
3. Match a Student's Letter Grade from a Score Table
You have a grading scale in cells F2:G6 where column F contains minimum scores (0, 60, 70, 80, 90) and column G contains letter grades (F, D, C, B, A). You want to convert a numeric score in cell B2 into a letter grade.
Description: "Convert the score in B2 to a letter grade using the grading table F2:G6"
=VLOOKUP(B2, F2:G6, 2, TRUE)This is one of the best use cases for approximate match (TRUE). A score of 85 does not appear in the table, but VLOOKUP finds the largest value that is less than or equal to 85 (which is 80) and returns "B". Important: the first column of the lookup table must be sorted in ascending order for approximate match to work correctly.
4. Use VLOOKUP with Approximate Match for Tax Brackets
You have a tax bracket table in H2:I6 where column H contains income thresholds (0, 10000, 40000, 85000, 165000) and column I contains the corresponding tax rates (10%, 12%, 22%, 24%, 32%). You want to find the tax rate for an income in cell D2.
Description: "Find the tax rate for income in D2 using the tax bracket table H2:I6"
=VLOOKUP(D2, H2:I6, 2, TRUE)An income of $55,000 falls between $40,000 and $85,000. VLOOKUP with approximate match returns the rate associated with $40,000 (22%), because $40,000 is the largest threshold that does not exceed $55,000. This pattern works perfectly for tiered pricing, commission brackets, shipping rates, and any scenario where values fall into ranges.
5. VLOOKUP with IFERROR to Handle Missing Values
When the lookup value does not exist in the table, VLOOKUP returns a #N/A error. You can wrap it in IFERROR to display a friendlier message instead.
Description: "Look up the price for the product in E1. Show 'Not found' if the product doesn't exist"
=IFERROR(VLOOKUP(E1, A:C, 3, FALSE), "Not found")This is a common production pattern. The IFERROR wrapper catches any error from VLOOKUP and returns the text "Not found" instead of displaying a raw error in your spreadsheet.
Common VLOOKUP Errors
Even experienced spreadsheet users run into these VLOOKUP pitfalls. Understanding why they happen helps you troubleshoot faster.
#N/A Error
The most common VLOOKUP error. It means the lookup value was not found in the first column of the table array. Common causes include:
- Extra whitespace — Leading or trailing spaces in cells. Use
TRIM()to clean your data. - Data type mismatch — The lookup value is a number but the table stores it as text (or vice versa). Ensure both are the same type.
- Value genuinely missing — The item does not exist in the table. Use
IFERROR()to handle this gracefully.
Wrong Column Index
The col_index_num is relative to the table_array, not the worksheet. If your table starts at column C, column C is index 1, column D is index 2, and so on. A common mistake is using the worksheet column number instead of the relative position within the table range.
Unsorted Data with Approximate Match
When using TRUE (approximate match), the first column of the table must be sorted in ascending order. If it is not sorted, VLOOKUP can return incorrect results without any error message, which makes this bug particularly hard to catch.
#REF! Error
This occurs when the col_index_num is larger than the number of columns in the table_array. For example, if your table has 3 columns but you specify col_index_num = 5, you will get a #REF! error. Double-check your range and column number.
VLOOKUP vs XLOOKUP vs INDEX MATCH
VLOOKUP is the classic lookup function, but newer alternatives offer more flexibility. Here is how they compare:
| Feature | VLOOKUP | XLOOKUP | INDEX MATCH |
|---|---|---|---|
| Lookup direction | Left to right only | Any direction | Any direction |
| Default match | Approximate | Exact | Exact |
| Column insertion safe | No (breaks col_index) | Yes | Yes |
| Built-in error handling | No | Yes (if_not_found) | No |
| Platform support | All platforms | Excel 365 / Sheets | All platforms |
| Ease of use | Easiest | Easy | Moderate |
When to use VLOOKUP: Quick lookups where the lookup column is the first column and you do not need to look left. VLOOKUP is the simplest syntax and is universally supported.
When to use XLOOKUP: When you need to look up values in any direction, want built-in error handling, or need a formula that does not break when columns are inserted. See our XLOOKUP guide for details.
When to use INDEX MATCH: The most flexible option for complex lookups, multiple criteria, or when you need the formula to be robust against structural changes. See our INDEX MATCH guide.
Platform Differences: Excel vs Google Sheets vs Apple Numbers
VLOOKUP works across all three major spreadsheet platforms, but there are subtle differences worth knowing:
Microsoft Excel
- VLOOKUP has been available since Excel 97 and works in all versions.
- Excel 365 and Excel 2021 also support XLOOKUP as a modern alternative.
- Supports structured table references, e.g.,
=VLOOKUP(E1, Table1, 3, FALSE). - The
@implicit intersection operator in Excel 365 can affect VLOOKUP behavior in tables.
Google Sheets
- VLOOKUP syntax is identical to Excel.
- Google Sheets also supports XLOOKUP (added in 2023).
- You can use VLOOKUP with
IMPORTRANGE()to look up data across different spreadsheets. - Performance can differ with very large datasets; consider QUERY as an alternative for heavy data work.
Apple Numbers
- VLOOKUP is fully supported with the same syntax.
- Apple Numbers does not support XLOOKUP. Use VLOOKUP or INDEX/MATCH instead.
- Cell references use a different notation (e.g., table-based references), but the VLOOKUP arguments remain the same.
FormulaAI automatically generates the correct syntax for your chosen platform, so you do not need to worry about these differences. Just select Excel, Google Sheets, or Apple Numbers before converting.
Related Guides
- XLOOKUP Guide — The modern replacement for VLOOKUP in Excel 365 and Google Sheets.
- INDEX MATCH Guide — The most flexible lookup combination for advanced use cases.
- SUMIF & SUMIFS Guide — Conditional summing formulas for totals by category.
- Fix the #REF! Error — when a too-large VLOOKUP column index breaks the formula.
- Apple Numbers Formula Help — VLOOKUP works the same in Numbers.
- All formula guides — browse every guide.
- FAQ — Common questions about FormulaAI and supported formulas.