In Excel, VLOOKUP searches the first column of a range and returns a value from another column in the same row. For an exact match, use =VLOOKUP(E2,A2:C6,3,FALSE); this searches for the value in E2 in column A and returns the corresponding value from the third column of A:C.
How VLOOKUP works
VLOOKUP is useful when a table has a key in its leftmost column—such as an employee ID, product code, or postal code—and you want to retrieve related information from a column to its right. The function name refers to a vertical lookup: it checks rows in a column rather than searching across a row.
The function takes four arguments: the value to find, the full table range, the number of the return column within that range, and the match mode. The column number is counted from the left edge of the selected range, not from the worksheet. Thus, in a range beginning in column C, its first column is number 1.
- Lookup value: The value to search for, often a cell reference such as E2.
- Table range: The cells containing both the lookup column and the return column.
- Column index: A number identifying which range column contains the answer.
- Match mode: FALSE requests an exact match; TRUE requests an approximate match.
For reliable results, specify the fourth argument instead of leaving it out. If it is omitted, VLOOKUP uses approximate matching, which can return an unexpected result when a table is not arranged for that kind of lookup.
Exact-match example: find a department
Exact matching is the usual choice for identifiers such as product codes, order numbers, or employee IDs. Suppose cells A2:B6 contain employee IDs in column A and department names in column B, while E2 contains the ID to find. This formula returns the department for ID 1025 if that ID is in the table:
With employee IDs in A2:A6, departments in B2:B6, and the lookup ID 1025 in E2, enter:
=VLOOKUP(E2,A2:B6,2,FALSE)
If the row for 1025 contains Sales in column B, the result is Sales. The 2 means “return the second column of A:B,” and FALSE tells Excel not to substitute a nearby ID if 1025 is absent. If no exact ID exists, the usual result is #N/A.
The search column must be the leftmost column of the table range. If IDs are in column B and the desired department is in column A, changing the column index will not make VLOOKUP search to the left; the range would need to be rearranged or another lookup method used.
Return a different column from a wider table
The column index counts every column in the selected range, including columns you do not plan to return. For example, if a product table has item code in A, description in B, and unit price in C, the price is column 3 of A:C. With a code in F2, the formula can retrieve its price:
With item codes in A2:A6, descriptions in B2:B6, prices in C2:C6, and the requested code in F2, enter:
=VLOOKUP(F2,A2:C6,3,FALSE)
If F2 contains an item code whose price in column C is 12.50, the result is 12.50. This works because the code is in the range’s first column and the price is its third. If you instead select B2:C6, the item code is no longer part of the range, so Excel cannot find it there.
Copy the formula without shifting its table
When you fill a lookup formula down a column, the lookup reference should usually change by row, but the source table should stay fixed. Dollar signs make a reference absolute, preventing its row or column from shifting when the formula is copied.
With the same product table in A2:C6, a code in E2, and the formula intended to be filled down, enter:
=VLOOKUP(E2,$A$2:$C$6,3,FALSE)
For a code whose price is 12.50, this returns 12.50. When copied into the next row, E2 adjusts to E3, while $A$2:$C$6 remains fixed. In Excel for Microsoft 365, selecting a reference while editing and pressing F4 cycles through reference styles on Windows; the equivalent shortcut on Mac can depend on the keyboard and system settings, so typing the dollar signs is a dependable alternative.
Approximate-match example: assign a rating
Approximate matching is appropriate when the first column contains sorted lower boundaries, such as score thresholds, shipping bands, or commission tiers. With TRUE, VLOOKUP returns the row for the largest first-column value that is less than or equal to the lookup value. The first column must be sorted in ascending order for this use.
Suppose G2:H5 contains these pairs: 0 and Fail, 60 and Pass, 75 and Merit, and 90 and Distinction. The minimum scores in G2:G5 are in ascending order. If D2 contains a score of 82, use:
With score thresholds in G2:G5, their rating labels in H2:H5, and the score 82 in D2, enter:
=VLOOKUP(D2,$G$2:$H$5,2,TRUE)
The result is Merit, because 75 is the largest threshold not greater than 82. A score of 90 would return Distinction, while a score of 59 would return #N/A because it is below the smallest threshold. This is a threshold lookup, not a nearest-value lookup: a score of 82 does not round up to the 90 band.
Do not use approximate matching on an unsorted list of employee IDs or product codes. It is not a substitute for an exact match, and a poorly ordered table can produce a result that looks plausible but is wrong.
Wildcard example: match part of a text value
VLOOKUP can use wildcards for text searches when the match argument is FALSE. An asterisk represents any number of characters, and a question mark represents one character. A tilde can be used before a wildcard when you need to search for the literal asterisk or question mark itself.
Suppose A2:B5 lists product codes in column A and product names in column B, and the first code ending in “-XL” is TSHIRT-XL with the name Blue T-shirt. This formula finds the first code matching the pattern and returns its name:
With product codes in A2:A5, product names in B2:B5, and the matching code TSHIRT-XL as the first code ending in -XL, enter:
=VLOOKUP("*-XL",A2:B5,2,FALSE)
The result is Blue T-shirt. The asterisk can match the “TSHIRT” portion. If multiple codes end in -XL, VLOOKUP returns the first matching row in the table; it does not return every match.
Handle missing values and common errors
An exact lookup commonly produces #N/A when the requested value is not in the first column. One option is to display a readable message instead. IFERROR catches errors generated by the full expression, so it can also hide errors other than a missing match; use it thoughtfully when diagnosing a workbook.
With employee IDs in A2:A6, departments in B2:B6, and a value in E2 that may not appear in the ID list, enter:
=IFERROR(VLOOKUP(E2,A2:B6,2,FALSE),"Not found")
If E2 contains 9999 and that ID is absent, the result is Not found. If it contains an ID assigned to Sales, the result is Sales. A message can make a report easier to read, but it may also conceal a structural problem, so check the source table if a result seems unexpected.
Other frequent causes of trouble include:
- Wrong column index: The index is larger than the number of columns in the selected range, which can cause #REF!. Count from the range’s first column.
- Numbers stored as text: A numeric-looking ID in one place may be text in another. Check that both sides use compatible data types.
- Unwanted spaces: Leading or trailing spaces in text can prevent a match. Clean or standardize the source values rather than assuming the strings are identical.
- Wrong range: The lookup column must be included and placed at the left edge of the range.
- Duplicate keys: When exact matches appear more than once, VLOOKUP returns the first matching row from top to bottom. Make keys unique if a particular record is required.
VLOOKUP does not distinguish uppercase from lowercase letters. For example, it treats “abc” and “ABC” as equivalent for ordinary matching. If capitalization is meaningful in your data, use a case-sensitive approach rather than relying on VLOOKUP alone.
VLOOKUP compared with newer lookup options
VLOOKUP remains convenient when the lookup value is in the leftmost column and the table layout is stable. Its main structural limitation is that it returns values only from columns to the right of the lookup column. Inserting or deleting columns inside a range can also make a hard-coded column index less intuitive, so it is worth checking formulas after changing the table structure.
In Microsoft 365 and Excel 2021 or later, XLOOKUP offers a more flexible alternative: you specify the lookup range and return range separately, so the return data can be on either side of the search column. For example, with IDs in A2:A6, departments in B2:B6, and the ID to find in E2, this formula returns Sales for an ID assigned to that department:
=XLOOKUP(E2,A2:A6,B2:B6,"Not found")
XLOOKUP can also provide a not-found result directly, as shown. If a workbook must support older Excel releases that do not include XLOOKUP, INDEX with MATCH is a common alternative. With IDs in A2:A6, departments in B2:B6, and the lookup ID in E2, this exact-match formula returns the department associated with that ID:
=INDEX(B2:B6,MATCH(E2,A2:A6,0))
The zero in MATCH requests an exact match. These alternatives do not make VLOOKUP incorrect; they simply avoid some of its leftmost-column and fixed-index constraints. Choose based on the Excel versions used by everyone who needs to open the workbook.
Frequently Asked Questions
What does FALSE mean in VLOOKUP?
FALSE requests an exact match. It is usually the safest choice for codes, IDs, and names where a near value must not be treated as a match.
What happens if I leave out the last VLOOKUP argument?
Excel treats the omitted match argument as TRUE, which requests approximate matching. For a regular ID lookup, include FALSE explicitly to avoid accidental approximate results.
Can VLOOKUP look to the left?
No. The value to search must be in the leftmost column of the selected range, and VLOOKUP returns a value from a column to its right. Use XLOOKUP in Microsoft 365 or Excel 2021 and later, or INDEX with MATCH, when the return column is left of the lookup column.
Why does VLOOKUP return #N/A when the value appears in the table?
Check for extra spaces, mismatched text-versus-number formats, a lookup range that starts in the wrong column, or a value that is not actually identical. With approximate matching, also confirm that the first column is sorted in ascending order.
Does VLOOKUP return every matching row?
No. It returns one result: the first match it encounters from the top of the selected range. To retrieve multiple matching records, use a different approach, such as filtering the data in a version of Excel that supports dynamic array functions.
Related
- Excel Strikethrough Shortcut: Windows, Mac, and More
- Stop Excel Converting Text to Dates Before It Happens
- Sorting by Dates in Excel: A Clear Step-by-Step Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Text Plus Cell Value: Formulas, Formatting, and FixesTo combine text with a cell value in Excel, join them with the ampersand operator (&). For example,…
- Excel Median IF: Multiple Criteria Made EasyTo calculate a median in Excel based on multiple criteria, use an array formula with the MEDIAN and…
- How to Filter Highest to Lowest in Excel: A Quick GuideTo show the largest values first in Excel, open the filter arrow on the column you want to…
- Excel Time Conversion Formula: Hours, Minutes, and MoreIf A2 contains an Excel time, multiply it by 24 to get decimal hours: =A2*24. For decimal minutes,…
- Excel Left Trim Formula: Remove Characters from StartThe quickest way to left-trim (remove characters from the start of) a text string in Excel is with…
- How to Add Hours in Excel: Formulas and Time FormatsTo add hours in Excel, enter each duration in a cell, total the cells with SUM, then format…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.