In Excel, VLOOKUP finds a value in the first column of a range and returns related information from a column to its right. For an exact match, use =VLOOKUP(E2,A2:C6,3,FALSE); with the lookup value in E2 and a three-column table in A2:C6, this returns the matching row’s third-column value.
What VLOOKUP does and when to use it
VLOOKUP is short for “vertical lookup.” It searches down the leftmost column of a selected table, finds a matching value, and returns a value from the same row. It is useful when you have a key—such as an employee ID, product code, or postal code—and want to retrieve a name, department, price, or other associated detail.
For example, a product list might have product codes in column A, descriptions in B, and prices in C. If you enter a code in another cell, VLOOKUP can return the corresponding description or price. The source table can be on the same worksheet or a different worksheet, provided the formula refers to the correct range.
VLOOKUP does not merge records or search an entire workbook automatically. You tell it where to look, which column to return, and whether to require an exact match. Its main limitation is directional: the lookup column must be the first column of the selected range, and the result must be in a column to its right.
VLOOKUP syntax, explained simply
The function has four arguments: =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup). In everyday terms, these mean:
- lookup_value: The value to find, often a cell reference such as E2.
- table_array: The range containing both the lookup column and the return column, such as A2:C6.
- col_index_num: The number of the return column within that selected range. The first column of the range is 1, the second is 2, and so on.
- range_lookup: Use FALSE for an exact match or TRUE for an approximate match. If you leave this argument out, Excel uses TRUE, so beginners should normally specify FALSE.
That column number is relative to the range, not the worksheet. If your table starts in column D, its first column still has index 1. This detail is a frequent source of incorrect results: a formula can be valid but return the wrong field if its column index is counted from the worksheet edge instead of from the start of the selected range.
Example 1: Find a department from an employee ID
Imagine a worksheet with employee IDs in A2:A6, names in B2:B6, and departments in C2:C6. The records are 1042, Ana Ruiz, West; 1043, Ben Chen, East; 1044, Cara Patel, North; 1045, Devin Lee, West; and 1046, Erin Moss, South. Enter the ID 1044 in E2. With those records in A2:C6 and the lookup ID in E2, enter =VLOOKUP(E2,A2:C6,3,FALSE). The result is North.
Excel searches only the first column of A2:C6 for 1044. When it finds that ID, it moves across to the third column of the selected range, which is worksheet column C, and returns North. The final FALSE requires an exact match, so the function will not substitute a nearby employee ID if 1044 is absent.
To return the employee’s name from the same table, change the column index from 3 to 2. With the same range and E2 value, =VLOOKUP(E2,A2:C6,2,FALSE) returns Cara Patel. This is a useful way to see why the index counts columns inside A2:C6: B is the second column of that range.
Example 2: Look up a product price
Suppose A2:C5 contains product codes, descriptions, and prices: P-101, Notebook, 2.50; P-102, Pen, 1.25; P-103, Folder, 3.00; and P-104, Marker, 1.75. Put P-102 in E2. With the product data in A2:C5 and the code in E2, use =VLOOKUP(E2,A2:C5,3,FALSE). The result is 1.25, the value in the third column of the matching row.
To return the description instead, use a column index of 2. With the same layout, =VLOOKUP(E2,A2:C5,2,FALSE) returns Pen. In a real price list, format the result cell as currency using Home > Number so it displays with an appropriate currency symbol and decimal places. Number formatting changes how a value appears, not the underlying lookup result.
These examples also show why the table range must include both the key column and the requested result column. Selecting only B2:C5 would not work for a search on product codes, because the codes in column A would no longer be the first column in the selected range.
Exact matches and approximate matches
For IDs, names, codes, and most ordinary lookups, choose FALSE. An exact match either finds the requested value or returns an error. This avoids a particularly confusing beginner mistake: using the default approximate-match behavior on an unsorted list and receiving a plausible but incorrect result.
TRUE is intended for threshold tables, such as tax bands, shipping brackets, or commission tiers. With approximate matching, VLOOKUP finds the largest value in the first column that is less than or equal to the lookup value. The first column must be sorted in ascending order for the result to be dependable. A value smaller than the first threshold has no qualifying match and returns #N/A.
For a commission example, suppose A2:B5 contains minimum sales and rates: 0 and 0%; 1000 and 5%; 5000 and 8%; and 10000 and 10%. If D2 contains sales of 6200, with the threshold table in A2:B5 and the sales amount in D2, enter =VLOOKUP(D2,A2:B5,2,TRUE). The result is 8%, because 5000 is the largest threshold not greater than 6200. The thresholds must remain in ascending order; sorting them differently can produce an unreliable result.
How to make a VLOOKUP easier to copy
If you copy a formula down a list, the lookup-cell reference should usually change from E2 to E3, E4, and so on. The source table range, however, should usually stay fixed. Add dollar signs to make that range absolute. With IDs in E2:E6 and the employee table in A2:C6, enter =VLOOKUP(E2,$A$2:$C$6,3,FALSE) in F2 and copy it down. Each row checks its own ID while continuing to search the same table.
Without the dollar signs, Excel adjusts the table range as the formula moves. A copied formula might start searching A3:C7 instead of A2:C6, which can omit the first record and include an unrelated later row. Absolute references prevent that shift. You can type the dollar signs directly; on Excel for Windows, F4 can cycle through reference styles while editing a reference in a formula.
It is also sensible to keep lookup values and source keys in consistent formats. A numeric ID stored as a number may not match an ID stored as text, even when both cells look like 1044. If an apparently identical value returns #N/A, check the data type, extra spaces, and whether the characters are genuinely the same.
Common VLOOKUP errors and how to fix them
#N/A usually means an exact match was not found. Check for a typo, leading or trailing spaces, inconsistent number-versus-text formats, or a lookup value that is missing from the first column. If a missing result is expected and you want a clear message instead of an error, use IFNA. With the employee table in A2:C6 and a lookup value in E2 that is not present, enter =IFNA(VLOOKUP(E2,A2:C6,3,FALSE),"Not found"). For an absent ID such as 9999, the displayed result is Not found. IFNA replaces #N/A specifically; it does not hide other formula errors.
#REF! can appear when the column index asks for a column outside the table range. For example, an index of 4 cannot return a value from A2:C6, because that range contains only three columns. Count the columns from the left edge of the selected range and make sure the requested field is included.
#VALUE! may indicate an invalid argument, while a wrong but error-free answer often points to a mistaken column index or an unintended approximate match. Confirm that the last argument is FALSE for an exact lookup, that the table starts with the key field, and that the index identifies the intended return column.
VLOOKUP returns the first matching row it encounters. If the lookup column contains duplicate IDs or product codes, it does not report every match; it returns the associated value from the first matching row in the range. If duplicates should not exist, check the source data rather than assuming the formula will choose the intended record.
VLOOKUP limitations and alternatives
VLOOKUP cannot return information from a column to the left of its lookup column. One workaround is to rearrange the source columns, but that may be inconvenient or unsuitable for shared data. Excel for Microsoft 365 includes XLOOKUP, which can look in one range and return a result from another range without requiring the lookup range to be on the left. XLOOKUP is also available in Excel 2021; it is not available in older perpetual Excel versions such as Excel 2019.
For example, with employee IDs in A2:A6, names in B2:B6, and departments in C2:C6, and an ID in E2, =XLOOKUP(E2,A2:A6,B2:B6,"Not found") returns the matching name or the message Not found. Unlike VLOOKUP’s column index, the return range is stated directly. XLOOKUP is often easier to maintain if someone inserts a column into the source table, because its result range is not selected by a number counted across a wider table array.
VLOOKUP is still useful when you need compatibility with older Excel versions or when a workbook already uses it. INDEX and MATCH are another alternative in many Excel versions and can look in either direction, though their combined syntax is less approachable for many beginners. The right choice depends on who will use the workbook, which Excel versions they have, and whether the source layout is likely to change.
A short checklist before relying on a lookup
Before using a VLOOKUP result in a report or workflow, check the formula and the data rather than relying only on a plausible-looking answer. A quick review can catch mistakes that do not produce visible errors:
- Is the lookup key in the first column of the selected range?
- Does the column index point to the intended field within that range?
- Does the formula specify FALSE for an exact match?
- Are the source keys unique when each key is supposed to identify one record?
- Are lookup values and source keys stored consistently, with no unwanted spaces?
- Will copying the formula move the lookup reference but keep the source range fixed?
For important workbooks, test a key you know should match, one you know should be missing, and—if duplicates are possible—a key that occurs more than once. Those checks help reveal both formula errors and problems in the underlying data.
Frequently Asked Questions
What does FALSE mean in VLOOKUP?
FALSE tells VLOOKUP to require an exact match. It is the usual choice for employee IDs, product codes, and other values that must match precisely.
Why does VLOOKUP return #N/A when the value is there?
The displayed values may differ because of extra spaces, a number stored as text, a typo, or a different character. Also confirm that the lookup value is being searched in the first column of the selected range.
Can VLOOKUP search to the left?
No. The lookup column must be the first column in the table array, and VLOOKUP returns values from columns to its right. XLOOKUP in Microsoft 365 or Excel 2021 can return a value from a separate range on either side.
What happens if there are duplicate lookup values?
VLOOKUP returns the result from the first matching row it finds in the selected range. It does not list all matching rows, so check for duplicates if each key is expected to be unique.
Do I have to sort the table for VLOOKUP?
Not when you use FALSE for an exact match. When you use TRUE for an approximate match, sort the first column in ascending order and use the function only for threshold-style lookups where the nearest lower threshold is the intended result.
Related
- Find and Replace Font in Word: Full Steps and Tips
- Find and Replace Highlight Color in Microsoft Word
- Find and Replace in Word: Open and Use the Dialog Box
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Days Until Due: Formula, Date Math, and Alerts ExplainedTo calculate days until a due date in Excel, put the due date in A2 and enter =A2-TODAY()…
- Excel INDEX MATCH Examples and How to Use ThemIn Excel, INDEX MATCH looks up a value by finding its position with MATCH and returning the value…
- Split a Cell in Excel: Text to Columns, Formulas, and MoreYes. In Microsoft Excel, select the cells and choose Data > Text to Columns to split their contents…
- XLOOKUP Entry Examples: Formulas for Common SearchesXLOOKUP entry examples show how to find a value in one range and return its matching value from…
- How to Create Box File Labels in Word That Fit RightTo create box file labels in Word, measure the label area, set up a page or table to…
- Dynamic Named Range Excel Formula: Setup and ExamplesA dynamic named range in Excel is a named reference that expands or contracts as entries are added…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.