VLOOKUP in Excel searches for a value in the first column of a table and returns a related value from a column to its right. For example, if product IDs are in A2:A6 and prices are in C2:C6, enter =VLOOKUP(E2,A2:C6,3,FALSE) to find the ID in E2 and return its price; FALSE requests an exact match.
What VLOOKUP does
The name stands for “vertical lookup”: Excel searches down a column, rather than across a row. It is useful when you have a key—such as an employee number, product code, or postal code—and want to retrieve another piece of information from the same record. The lookup value does not have to be a number; it can be text or a date, provided its type and value match what is in the table.
VLOOKUP returns one value from one row. It does not combine records, search every column, or automatically return several columns at once. Its defining restriction is that the column containing the lookup values must be the leftmost column of the selected table range. The return column must be somewhere to its right.
VLOOKUP is available in Excel for Microsoft 365 on Windows, as well as in many earlier Excel versions. Its basic syntax has remained consistent, which is one reason older workbooks still use it. In Microsoft 365, newer lookup functions can handle cases that VLOOKUP cannot, but VLOOKUP remains practical for straightforward left-to-right lookups and for compatibility with older files.
VLOOKUP syntax and its four arguments
The function has this structure: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]). The square brackets indicate that the last argument is optional; they are not typed into the formula. Each argument tells Excel what to search and what to return:
- lookup_value: The value Excel should find, often a cell reference such as E2.
- table_array: The range containing both the lookup column and the return column, such as A2:C100.
- col_index_num: The number of the return column counted from the left edge of table_array. In A2:C100, A is 1, B is 2, and C is 3.
- range_lookup: TRUE or omitted for an approximate match, or FALSE for an exact match. Zero is another way to request an exact match.
The column number is relative to the selected range, not the worksheet. If the range starts in column D, then D is column 1 of that range, even though it is column 4 on the sheet. The number must be at least 1 and cannot exceed the number of columns in table_array.
Exact match versus approximate match
For codes, names, IDs, and most ordinary lookups, use FALSE (or 0) as the fourth argument. This tells Excel to find the same value. If there is no exact match, VLOOKUP returns #N/A. Exact-match formulas do not require the lookup column to be sorted.
TRUE requests an approximate match. In that mode, Excel expects the first column of the range to be sorted in ascending order. It looks for an exact value if available; otherwise, it returns the result associated with the largest first-column value that is less than or equal to the lookup value. If the data is not sorted as expected, the answer can be incorrect without an obvious warning. Omitting the fourth argument also selects approximate matching, so it is safer to state FALSE or TRUE explicitly.
Approximate matching can be useful for threshold tables, such as assigning a shipping band based on package weight or a grade based on a score. With thresholds 0, 60, 80, and 90 in ascending order, a lookup of 82 matches the 80 threshold. It does not mean “find the closest value” in either direction: a threshold above 82 is not selected.
A practical exact-match example
Suppose a small inventory table has product IDs in A2:A6, names in B2:B6, and prices in C2:C6. Cell E2 contains the ID to look up. The range begins in A, so the price is the third column of the selected range:
With IDs in A2:A6, names in B2:B6, prices in C2:C6, and the requested ID in E2, use:
=VLOOKUP(E2,A2:C6,3,FALSE)
If E2 contains P104 and row 5 contains P104, Desk lamp, and 24.99 in A5:C5, the result is 24.99. If you want the name instead, the return column is the second column in A2:C6:
With the same table layout and the product ID in E2, use:
=VLOOKUP(E2,A2:C6,2,FALSE)
This returns Desk lamp for P104. The lookup range must include the columns from the ID through the requested result; selecting only A2:A6 leaves no second or third column to return.
How to enter and copy a formula
On Excel for Microsoft 365 for Windows, select the cell where you want the answer, type the formula, and press Enter. To reuse the formula for multiple lookup values, copy it down with the fill handle or select the formula cell and the cells below it, then press Ctrl+D. On Mac, the fill handle also works; Command+D is the corresponding Fill Down shortcut in Excel for Mac. Excel for the web supports VLOOKUP and the fill handle, though keyboard shortcuts can vary with browser settings.
When copying a formula, decide whether the table range should move. If you copy =VLOOKUP(E2,A2:C6,3,FALSE) down one row, Excel normally adjusts it to E3 and A3:C7. That may be wrong because the lookup table has shifted. Lock the table range with dollar signs so it stays fixed:
With the lookup table fixed in A2:C6 and lookup values beginning in E2, use:
=VLOOKUP(E2,$A$2:$C$6,3,FALSE)
When filled down, E2 changes to E3, E4, and so on, while $A$2:$C$6 remains anchored. In Excel, you can select a range reference while editing a formula and press F4 on Windows to cycle through reference styles. On Mac, the shortcut may depend on keyboard settings; typing the dollar signs directly is a dependable alternative.
Common VLOOKUP errors and how to fix them
- #N/A: With FALSE, this usually means Excel did not find an exact match. Check for extra spaces, misspellings, or a number stored as text on one side and as a number on the other. Confirm that the lookup value is actually in the first column of the selected range.
- #REF!: The column index is greater than the number of columns in table_array, or a referenced range was removed. Count the columns from the range’s left edge and adjust the index or range.
- #VALUE!: The column index is less than 1 or another argument is invalid. Check that col_index_num is a positive number.
- A plausible but wrong result: The formula may be using approximate matching because the fourth argument is TRUE or missing. Use FALSE for exact lookups. If approximate matching is intentional, sort the first column in ascending order.
- Wrong column returned: Recount from the left edge of table_array, not from worksheet column A. Also check that the range begins with the lookup column.
VLOOKUP does not distinguish uppercase from lowercase in ordinary text comparisons. If your data contains repeated lookup values, an exact-match lookup returns the first matching row it encounters from top to bottom. If a code appears more than once, verify that this first occurrence is the record you intend to retrieve.
Wildcards, duplicates, and data quality
With exact matching, text searches can use wildcards: an asterisk (*) represents any number of characters, and a question mark (?) represents one character. A tilde (~) before a wildcard character treats it as a literal character. For instance, searching for a text pattern beginning with “North” and followed by any characters can find a matching entry such as North Depot. Wildcards are useful for partial text searches, but they can also match more than one row; VLOOKUP still returns only the first match.
For dependable results, keep lookup keys consistent. A product code with a leading zero may be stored as text, while a similar-looking value may be stored as a number. Spaces imported from another system can also make two visible strings unequal. Formatting alone does not always change the underlying value. If a lookup fails unexpectedly, inspect the source cells and compare the actual stored values rather than assuming that identical display means identical data.
VLOOKUP limits and alternatives
VLOOKUP cannot return a value to the left of its lookup column. It also relies on a numeric column index, which can become fragile if someone inserts or rearranges columns inside the referenced table: the formula may still be valid but point to a different field. These constraints matter more in workbooks that change frequently or contain many columns.
XLOOKUP, available in Microsoft 365 and Excel 2021 or later, separates the lookup range from the return range and can search in either direction. With product IDs in A2:A6, prices in C2:C6, and the requested ID in E2, the formula is:
=XLOOKUP(E2,A2:A6,C2:C6,"Not found")
For that layout, the formula returns the corresponding price, or the text Not found if there is no match. XLOOKUP uses exact matching by default and does not require a column index number. A drawback is compatibility: people using older Excel versions may not be able to calculate XLOOKUP formulas.
INDEX with MATCH is another established option, including in older Excel versions. It can look left or right because the lookup range and result range can be specified separately. It takes more syntax than a simple VLOOKUP, but can be useful in workbooks that need flexible lookup direction or must support a broader range of Excel releases. For a new, simple left-to-right lookup where compatibility is important, VLOOKUP with FALSE remains a clear choice.
Frequently Asked Questions
What does the 3 mean in a VLOOKUP formula?
It is the return-column number counted from the left edge of table_array. In A2:C20, 3 means column C; it does not mean worksheet column 3 in every possible range.
Why does VLOOKUP return #N/A when the value is visible?
The displayed values may differ underneath because of spaces, text-versus-number differences, or a slightly different character. Also check that the lookup value is being searched for in the first column of the selected range and that the formula uses the intended match mode.
Does VLOOKUP search to the left?
No. It searches the first column of table_array and returns a value from a column to its right. Use XLOOKUP in supported Excel versions, or INDEX and MATCH, when the result is to the left.
Should I use TRUE or FALSE in VLOOKUP?
Use FALSE for an exact match, which is appropriate for most IDs, names, and codes. Use TRUE only when you want threshold-style approximate matching and the first column is sorted ascending.
Can VLOOKUP return multiple columns?
A standard VLOOKUP returns one result from one column per formula. You can create additional formulas with different column indexes, or use a suitable newer function such as XLOOKUP for a different lookup design. Availability of newer functions depends on the Excel version.
Related
- Vacation Meal Planning Template Excel: Plan Every Day
- Superscript in Excel: Formatting, Shortcuts, and Limits
- SMALL in Excel: Find the Nth Smallest Value with Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- EDATE in Excel: Move Dates Forward by One Month EasilyTo move a date forward one calendar month in Excel, put the original date in A2 and enter…
- Excel Counting Unique Values in a List: Formulas and TipsTo count distinct, nonblank values in an Excel list, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0), replacing A2:A100 with your range. In Microsoft…
- SUBTOTAL Formula Usage in Excel: Filters and ExamplesIn Excel, use the SUBTOTAL formula to calculate a sum, average, count, or other summary while automatically ignoring…
- COUNTIFS in Excel: Exact and Partial Text MatchesYes. In Excel, COUNTIFS can count cells that match text exactly or contain a text fragment: use =COUNTIFS(A2:A100,"Apple")…
- Excel AND and OR Functions Combined: How to Use ThemYes. You can combine Excel’s AND and OR functions by nesting one inside the other. For example, with…
- Excel Cell Contains String? Find Text With a FormulaTo check whether a cell contains a particular string of text, use =ISNUMBER(SEARCH("apple",A2)). With the word apple anywhere…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.