VLOOKUP in Excel looks for a value in the first column of a range and returns a related value from another column in the same row. Its purpose is to find matching information in a table—such as a product’s price from its code—without manually searching through rows.
What VLOOKUP Is Used For
The name means “vertical lookup”: Excel searches down a column, one row at a time, for a value you specify. When it finds a match, it can return a value from a different column on that row. This makes VLOOKUP useful when one column holds an identifier and other columns hold details associated with it.
For example, a stock list might have product codes in its first column, descriptions in its second, and quantities in its third. A separate order sheet can use a product code to retrieve the description or available quantity. Similarly, an employee number can retrieve a department, or a customer ID can retrieve a region. The formula can be copied to handle multiple lookup values, so the result updates when the input changes.
VLOOKUP does not search for a general relationship or decide which result is most appropriate. It follows the table layout and match rules you give it. In most everyday uses, you want an exact match, especially when looking up IDs, names, codes, or other distinct labels.
How the VLOOKUP Formula Works
The syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The arguments tell Excel what to find, where to look, which column to return, and whether to match exactly or approximately. The last argument is optional, but leaving it out can lead to an unexpected approximate match.
- lookup_value: The value to search for, such as a code in a cell.
- table_array: The range containing both the search column and the column with the answer.
- col_index_num: The number of the return column, counted from the left edge of the selected range. The first column is 1.
- range_lookup: FALSE requests an exact match; TRUE requests an approximate match. For a predictable exact lookup, specify FALSE.
VLOOKUP searches only the leftmost column of the selected range. It then counts across that range to return the requested column from the matching row. The column number is not necessarily the worksheet’s column letter: if the selected range starts in column C, its first column is still number 1.
A Product-Code Example
Suppose A2:C4 contains a product table: A has product codes, B has product names, and C has quantities. The lookup code is entered in E2. With the code P-104 in E2 and a row for P-104 showing Cable and 25, enter this formula in F2:
=VLOOKUP(E2,A2:C4,3,FALSE)
The formula searches the first column of A2:C4 for the value in E2. It returns the third column of that range, so the result is 25. To return the product name instead, change the column number from 3 to 2; the rest of the lookup can stay the same.
If you copy the formula down a list of lookup codes, Excel normally adjusts the range references as it moves. To keep the table range fixed while copying, use dollar signs: =VLOOKUP(E2,$A$2:$C$4,3,FALSE). In this version E2 can adjust to E3, E4, and so on, while the lookup table remains A2:C4. In a larger workbook, expand the range to include all relevant data rows.
Exact and Approximate Matches
For an exact lookup, use FALSE as the fourth argument. Excel returns a result only when it finds the lookup value as a match in the first column; otherwise, it returns #N/A. Exact match is generally the right choice for product codes, account numbers, employee IDs, and text labels. VLOOKUP returns the first matching row if the lookup column contains duplicates.
TRUE requests an approximate match. For this mode, the first column of the lookup range must be sorted in ascending order. Excel finds an exact value if available; otherwise, it uses the largest value that is less than the lookup value. This can suit a tier table, such as shipping bands or score thresholds, but it is risky for unsorted data. If the fourth argument is omitted, VLOOKUP uses approximate matching, so explicitly writing FALSE prevents that common mistake.
For example, suppose A2:B5 has ascending thresholds 0, 50, 100, and 150 in column A, with the labels Basic, Standard, Plus, and Premium in column B. D2 contains a score of 87. Use:
=VLOOKUP(D2,A2:B5,2,TRUE)
The result is Standard, because 50 is the largest threshold not greater than 87. If the score is below the smallest threshold, this setup returns #N/A; decide whether the table needs a lower starting threshold or a separate handling rule.
Table Layout and Data Requirements
Because VLOOKUP searches the first column of the selected range, arrange the data so the lookup field is at the left. If an answer is to the left of the lookup field, VLOOKUP cannot return it from that range. You can sometimes choose a different range, but you cannot simply ask VLOOKUP to search a middle column and return a value to its left.
Make sure the lookup value and the values in the search column have compatible data types. A number stored as text may not match the same digits stored as a number. Extra spaces can also make text values appear identical while preventing an exact match. Clean or standardize source data when necessary rather than relying on a formula to guess whether two differently stored values should count as the same.
The return-column number is counted within the range. If the selected range is B2:D100, B is 1, C is 2, and D is 3. A formula may keep working after rows are added, but inserting a column within the lookup range can change which field a fixed column number refers to. Check formulas after changing table structure.
Common VLOOKUP Errors and Fixes
- #N/A: Excel did not find an exact match, or an approximate lookup value is below the first threshold. Check spelling, spaces, data types, the search column, and the match mode.
- #REF!: The column number asks for a column beyond the selected range. For example, a range with three columns cannot return column 4. Widen the range or correct the index.
- #VALUE!: The column index is less than 1 or another argument is invalid. Check that the return-column number is a positive integer within the range.
- Wrong but plausible result: The formula may be using approximate matching on unsorted data, or the lookup column may contain duplicate values. Use FALSE for an exact lookup and review the table.
- Formula returns a value from the wrong field: Recount the return column from the left edge of table_array, not from worksheet column A.
For a temporary check, compare the lookup value directly with a value in the search column and inspect whether either has leading or trailing spaces. Also verify that the lookup range includes the row containing the expected result. If a formula is meant to be copied, lock the table range with dollar signs so it does not drift.
VLOOKUP’s Limits and Alternatives
VLOOKUP is a practical choice for straightforward tables, but its left-to-right design can constrain a workbook. It cannot return a value from a column to the left of the search column, and its numeric return-column index can become fragile when the table’s columns are rearranged. It also returns only one result—the first matching row—not every row that meets a condition.
In Microsoft 365 for Windows, XLOOKUP is often a more flexible option: it can look in one range and return from another, including a column to the left, and it lets you specify what to show when there is no match. XLOOKUP is also available in Excel 2021. INDEX and MATCH can provide flexible lookups in a wider range of Excel versions, though their formulas use separate functions. For listing multiple matching rows, filtering tools such as FILTER may be a better fit; FILTER is available in Microsoft 365 and Excel 2021.
These alternatives do not make VLOOKUP obsolete. If a workbook already uses VLOOKUP correctly, its structure is simple, and other users recognize the formula, keeping it may be sensible. Choose based on the direction of the lookup, whether the table may change, the Excel versions people use, and whether you need one match or several.
Frequently Asked Questions
What is the main purpose of VLOOKUP in Excel?
It searches the first column of a range for a value and returns a related value from another column in the matching row. It is commonly used to retrieve details from a reference table.
Should I use TRUE or FALSE in VLOOKUP?
Use FALSE for an exact match, which is appropriate for most IDs, codes, and names. Use TRUE only when an approximate match is intended and the first column is sorted in ascending order.
Why does VLOOKUP return #N/A when the value is there?
The values may differ because one is stored as text, contain extra spaces, or are not actually in the range’s first column. Check the exact lookup value, the selected range, and the data formatting.
Can VLOOKUP look to the left?
No. It searches the first column of its range and returns a value from a column to the right. Use XLOOKUP in Microsoft 365 or Excel 2021, or consider INDEX and MATCH, when you need a leftward lookup.
Does VLOOKUP return every matching row?
No. It returns a result from the first matching row it finds. If you need a list of all rows that meet a condition, use a filtering approach rather than VLOOKUP.
When VLOOKUP Is the Right Tool
Use VLOOKUP when your lookup key is in the leftmost column of a table and you need one related value from the same row. For most ordinary lookups, specify FALSE, confirm the range and column index, and check that the data is consistently stored. Those habits make the function’s purpose—and the result it returns—clear and dependable.
Related
- Excel to PowerPoint Linked Cells: How to Link and Update
- Convert Excel to PowerPoint: Methods, Steps, and Tips
- Convert PowerPoint to Excel: Steps, Options, and Limits
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUM Times Formula: Multiply Values, Then Add ThemIf quantities are in B2:B5 and unit prices are in C2:C5, use =SUMPRODUCT(B2:B5,C2:C5) to multiply each quantity by…
- Excel Column Width to Match Row Height?Excel has no built-in setting that makes a column’s width equal a row’s height: column width is measured…
- How to Find a Z Value in Excel: Formulas and ExamplesTo calculate a z-score for a value in Excel, subtract the dataset mean from the value and divide…
- Excel Null Values: Blanks, Formulas, and Power QueryMicrosoft Excel does not have a special worksheet value called NULL in the way a database does. What…
- Long Invoice Template Excel: Layout and FormulasA long invoice template in Excel is a worksheet designed to handle many billable lines while keeping the…
- Excel Yes or No Formula: How to Return Either AnswerYes. To show “Yes” when a test is true and “No” when it is false, put the value…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.