VLOOKUP is one of the most-used functions in Excel and the single most-asked-about formula in interviews for any analyst, finance or operations role. Vertical Lookup searches the leftmost column of a table for a value, then returns a value from a specified column in the same row of that table. The function is the workhorse for joining data from one sheet to another, looking up prices in a price list, finding employee names from ID numbers, matching shipping zones to ZIP codes — anywhere you need to fetch related information based on a key value.
The syntax is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The lookup_value is what you are looking for. The table_array is the range that contains both the lookup column and the return column. The col_index_num is which column in the table to return — counting from 1 for the leftmost column. The range_lookup is TRUE for approximate match (default) or FALSE for exact match. Use FALSE for exact match in nearly every business case; TRUE has limited use and causes most VLOOKUP errors.
This guide walks through every aspect of VLOOKUP that matters in practice — the syntax broken down argument by argument, the difference between exact and approximate match, why you almost always want absolute references for table_array, the common errors (#N/A, #REF!, wrong values returned) and how to fix each, the modern XLOOKUP alternative that ships with Excel 365, and the legacy INDEX/MATCH combination that solves problems VLOOKUP cannot.
The principles do not change between Excel versions. VLOOKUP works in Excel 2007 through Excel 365, on Windows, Mac and the web. XLOOKUP requires Excel 365 or Excel 2021. INDEX/MATCH works everywhere. The vast majority of legacy spreadsheets you will encounter use VLOOKUP, so understanding it remains essential even as XLOOKUP increasingly becomes the preferred approach for new workbooks.
Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Use FALSE (or 0) as the last argument for exact match — this is what you want for nearly every business case. Use absolute references on table_array ($A$2:$D$100) so the formula does not break when copied. The leftmost column of the table_array must contain the lookup_value; VLOOKUP cannot look to the left. Use XLOOKUP instead in Excel 365 — it removes most of VLOOKUP's quirks.
Building a basic VLOOKUP starts with a clear data layout. Imagine a sheet with a price list in cells A1:B10 — column A contains product codes (PROD-001 through PROD-010) and column B contains prices. On a different sheet, you have a sales report listing products sold and want to add the price for each line. In cell C2 of the sales report, type =VLOOKUP(A2, Sheet1!A1:B10, 2, FALSE). Excel finds the product code from A2 in column A of Sheet1, then returns the value from the second column (the price) for that row.
The 2 in this formula is the col_index_num — telling VLOOKUP to return the value from the second column of the table_array. If your table had four columns and you wanted the third, you would use 3. If you wanted the leftmost column itself (the lookup column), you would use 1, although that is rarely useful. The col_index_num is fragile in one important way: if you insert a new column into the table_array, the column you wanted to return is now at a different position and your formula returns the wrong value or an error.
The range_lookup argument is where most VLOOKUP errors come from. The default is TRUE (or 1) which performs approximate match — VLOOKUP returns the value for the largest entry in the lookup column that is less than or equal to the lookup_value. Approximate match requires the lookup column to be sorted ascending; otherwise it returns wrong results silently. For business data lookups (product codes, employee IDs, ZIP codes) you want exact match — pass FALSE (or 0) explicitly as the last argument.
Always use FALSE for the range_lookup argument unless you have a specific reason for approximate match. The most common legitimate use of approximate match is grade lookup tables (score >= 90 returns A, >= 80 returns B, etc.) and tax bracket lookups. For everything else, FALSE prevents the silent wrong-answer errors that make VLOOKUP infamous in spreadsheets that have not been carefully written.
The value you are looking for in the leftmost column of the table_array. Can be a cell reference, a hardcoded value or the result of another formula. Must match the data type of the lookup column — a text "123" will not match a numeric 123. Trim spaces with TRIM() if your data has padding.
The range containing the lookup column and the return column. The leftmost column of this range is searched. Use absolute references ($A$1:$D$100) so the range does not shift when copied. Excel Tables (Ctrl+T) work too — Table[#All] references stay aligned automatically as the table grows.
The column number within the table_array to return — counting from 1 for the leftmost. Hardcoding the number is fragile because inserting columns shifts the position. MATCH("header", header_row, 0) creates a dynamic col_index_num that survives column inserts and is the standard pattern in robust VLOOKUP formulas.
FALSE (or 0) for exact match — what you want in nearly every case. TRUE (or 1, or omitted) for approximate match — only use for sorted bracket lookups like grades and tax tables. Always type FALSE explicitly even though it is optional; the default behavior is the source of most VLOOKUP errors in business spreadsheets.
Absolute references are essential for any VLOOKUP that will be copied to multiple cells. Without absolute references, the table_array shifts as the formula is copied — the lookup table moves with the formula and produces #N/A errors or wrong values. Type the formula with relative references first to confirm it works, then add the dollar signs to lock the table_array. The keyboard shortcut F4 cycles through reference types as you type — once for full absolute, twice for row-only, three times for column-only.
The most common pattern is fully absolute table_array: =VLOOKUP(A2, $D$2:$F$100, 2, FALSE). When this formula is copied down a column, the lookup_value reference (A2) increments to A3, A4, A5 — exactly what you want. The table_array reference ($D$2:$F$100) stays locked, so every formula looks in the same lookup table. The col_index_num and range_lookup are constants and need no special treatment.
Excel Tables (created with Ctrl+T) eliminate the absolute reference issue entirely. Convert your lookup data to a Table named PriceList. Now write =VLOOKUP(A2, PriceList, 2, FALSE) — the structured Table reference automatically expands as you add rows to PriceList, and there is no need for dollar signs. The col_index_num still works the same way; you can also use INDEX with structured references for even cleaner code.
The lookup_value side benefits from cleanup too. If your data may have leading or trailing spaces, wrap it in TRIM(): =VLOOKUP(TRIM(A2), $D$2:$F$100, 2, FALSE). If text vs number type mismatches are possible, force the lookup value to text with TEXT() or to number with VALUE() depending on which the table contains. Mismatched types are the most common cause of #N/A errors when the value visually appears to be in the table.
The lookup value was not found. Check for typos and case (VLOOKUP is case-insensitive but match must still occur). Check for hidden trailing spaces in either the lookup value or the table — TRIM() fixes most spacing issues. Check that the table's leftmost column actually contains your lookup value. Most #N/A errors come from data quality issues rather than the formula itself.
VLOOKUP treats "123" (text) and 123 (number) as different values. If the lookup column has numeric IDs but your data was imported as text, the lookup fails despite the visual match. Convert one side to match the other using VALUE() or TEXT(). Or run Data > Text to Columns on the column to force consistent typing across all rows.
The col_index_num is greater than the number of columns in table_array. If your table is A:C (3 columns) and you ask for col_index_num 4, Excel returns #REF!. Either expand the table_array or use a smaller col_index_num. This error sometimes appears after deleting columns from the table — the formula's col_index reference is now wider than the table.
Almost always caused by approximate match (range_lookup = TRUE or omitted) on an unsorted lookup column. Add FALSE explicitly as the fourth argument and the issue disappears. If you genuinely want approximate match, sort the lookup column ascending. Otherwise the silent wrong-value problem is one of the worst spreadsheet errors because it produces incorrect results without any warning.
VLOOKUP cannot look to the left. The lookup_value must be in the leftmost column of the table_array, and you can only return values from columns to the right. If your data has product codes in column B and product names in column A, you cannot use VLOOKUP to look up a product code and return the name. This is the single biggest VLOOKUP limitation and the reason INDEX/MATCH and XLOOKUP exist as alternatives that handle left-side lookups naturally.
The INDEX/MATCH combination has been the power-user alternative since long before XLOOKUP existed. =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) returns the same result as VLOOKUP without the column-position fragility and without the leftmost-column constraint. The lookup_range and return_range are independent, so they can be in any column order. INDEX/MATCH is faster on large datasets and immune to most of VLOOKUP's quirks.
XLOOKUP is the modern replacement that ships with Excel 365 and Excel 2021. =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) handles left-side lookups, multiple return columns, exact match by default, custom not-found values without IFERROR wrapping, and reverse search direction. XLOOKUP is the recommended approach for new formulas in any version that supports it.
For workbooks that may be opened in older Excel versions, stick with VLOOKUP for compatibility. For Excel 365 only, XLOOKUP is the right choice. INDEX/MATCH remains useful for cross-version compatibility plus the ability to handle left-side lookups; many advanced spreadsheets still use it heavily despite XLOOKUP's arrival, because the muscle memory is deep and the formula structure handles edge cases that even XLOOKUP cannot.
For wildcards in VLOOKUP, the asterisk (*) matches any sequence of characters and the question mark (?) matches any single character. =VLOOKUP("Acme*", $A$1:$B$100, 2, FALSE) finds the first row where the lookup column starts with "Acme" and returns the second column value. Wildcards work only with exact match (FALSE). For partial matches in the middle of a string, wrap the lookup value: "*Acme*" matches anywhere. Note that wildcards do not work with numeric values, only text.
VLOOKUP returns only the first match it finds. If your lookup column has duplicate values, VLOOKUP returns the value associated with the first duplicate (the topmost row). To return values from later duplicates, you need different functions — typically INDEX with MATCH inside an array formula, or in Excel 365 the FILTER function which returns all matching values. For most business data with unique keys, this is not an issue, but it catches out new users with denormalized data.
VLOOKUP across multiple sheets is straightforward. =VLOOKUP(A2, 'Sheet 2'!$A$1:$B$100, 2, FALSE) looks up A2 in Sheet 2 and returns the second column. Sheet names with spaces require single quotes around the name. The same syntax works for cross-workbook lookups when both files are open: =VLOOKUP(A2, '[Pricing.xlsx]Sheet1'!$A$1:$B$100, 2, FALSE). Cross-workbook references break when the source file is renamed or moved, so keep the dependency in mind.
For very large tables, VLOOKUP performance becomes a consideration. A worksheet with 100,000 rows of VLOOKUP formulas pointing at a 50,000-row lookup table can take many seconds to recalculate. INDEX/MATCH is roughly 2 to 3 times faster than VLOOKUP on the same data. XLOOKUP performance is similar to INDEX/MATCH. For massive workbooks, consider converting to Power Query for the join operation, which handles millions of rows in seconds without sheet-level recalculation cost.
The IFERROR wrapper is the standard pattern for handling missing values gracefully. =IFERROR(VLOOKUP(A2, $D$2:$F$100, 2, FALSE), "") returns an empty string instead of #N/A when the lookup fails. =IFERROR(VLOOKUP(...), "Not Found") returns the text "Not Found" instead. =IFERROR(VLOOKUP(...), 0) returns zero — useful when the result feeds into a calculation. Choose the fallback that makes sense for your downstream use; do not just paper over errors that should be investigated.
For numeric lookups where missing values should produce zero rather than blank, =IFERROR(VLOOKUP(...), 0) is the right pattern. For reports where missing should be visible, the empty string version may hide data quality problems that a Not Found marker would surface. The choice between the two depends on whether the spreadsheet's audience is the data owner (who should see and fix the missing values) or a downstream consumer (who needs clean output).
For audit-friendly spreadsheets, document each VLOOKUP with a comment or a notes column explaining what it is doing. "Lookup price from PriceList by ProductCode" in a notes column adjacent to the formula clarifies intent for the next person reading the spreadsheet. Excel comments (right-click > New Comment) attach hover text to specific cells; both approaches are valid and reduce the cognitive cost of returning to a workbook six months later.
Named ranges improve formula readability. Define the price list range as PriceList (Formulas > Name Manager) so VLOOKUP becomes =VLOOKUP(A2, PriceList, 2, FALSE) rather than the more cryptic absolute-reference version. Names follow the same scoping rules as cell references — workbook-scoped names are visible from any sheet; sheet-scoped names live within one sheet. Named ranges combined with Excel Tables produce the most readable lookup formulas in modern Excel work.
The history of VLOOKUP traces back to the original release of Excel 2.0 in 1987, where it shipped alongside HLOOKUP (horizontal version) and the more general LOOKUP function. The basic VLOOKUP API has not changed in nearly 40 years, which is why the function appears in every Excel workbook on every desktop. The arrival of dynamic arrays and XLOOKUP in 2019 was the first major rethink in decades, but the legacy install base of VLOOKUP-using workbooks remains enormous.
For learning VLOOKUP from scratch, the most efficient approach is to build small examples that exercise each argument. Start with a 5-row price list and 10-row sales sheet, write VLOOKUP without absolute references, copy the formula and watch it break. Add the dollar signs and watch it work. Then introduce IFERROR wrapping. Then convert the price list to a Table and rewrite the formula with the structured reference. Each step takes 5 minutes and produces deep understanding.
Standard since 1987, works in every version. Limited to leftmost-column lookup and rightward returns. Default approximate match is the source of most spreadsheet errors. Still appropriate for compatibility with older workbooks and as the universal teaching baseline for Excel lookups.
Modern replacement in Excel 365 and 2021. Handles left-side lookups, exact match by default, custom not-found values, multiple return columns and reverse search. Recommended for all new formulas in supported versions. Cleaner, more flexible and less error-prone than VLOOKUP for almost every use case.
Power-user combination from before XLOOKUP existed. =INDEX(return_range, MATCH(lookup, lookup_range, 0)) handles left-side lookups, faster than VLOOKUP on large data, immune to column-insert breakage. Works in every Excel version. Many advanced spreadsheets still use INDEX/MATCH for cross-version compatibility and edge case flexibility.
Returns multiple matching rows rather than just the first match. =FILTER(return_range, criteria_range = lookup_value) is the dynamic-array alternative when duplicates matter or you need to return multiple columns at once. Excel 365 only. Often replaces array-formula INDEX/MATCH workarounds with cleaner syntax for advanced lookups.
For the workbook that will be shared with users on different Excel versions, the safest path is VLOOKUP wrapped in IFERROR. Every supported version handles this combination correctly. XLOOKUP is the cleanest formula but breaks in older Excel versions; INDEX/MATCH is universal but more verbose. The choice should be driven by the audience for the workbook — if you control the version, use the most modern formula; if you are sending out a workbook to clients with unknown Excel versions, default to the conservative approach.
Power Query Excel built-in handles the most demanding lookup scenarios that overwhelm worksheet formulas. Loading two tables into Power Query, performing a Merge query to join them on a common key, and loading the result back to the worksheet replaces millions of VLOOKUP formulas with a single ETL step that runs in seconds. For analytics work with large datasets, Power Query is the right tool; VLOOKUP and friends remain right for smaller, in-place worksheet operations.
For interview preparation, VLOOKUP is the function asked about most often in entry-level finance and analyst roles. Common interview questions include: write a VLOOKUP that finds a price by product code; explain why you would use FALSE for the fourth argument; identify what is wrong with an unsorted approximate-match VLOOKUP; convert a VLOOKUP to INDEX/MATCH to handle a left-side lookup; and write an IFERROR wrapper for missing values. Practicing these on a small sample workbook produces the muscle memory that real-world spreadsheet work demands.
For the spreadsheet that already has hundreds of VLOOKUP formulas, do not feel obligated to convert them all to XLOOKUP just because the newer function is available. The maintenance cost of mass conversion usually outweighs the benefit, especially if the existing formulas are correct and well-tested. Reserve XLOOKUP for new formulas being added to the workbook and for VLOOKUPs that are actively breaking due to column-insert issues. Migration discipline matters in long-lived workbooks.