To use VLOOKUP in Excel, put the value you want to find in the first column of a lookup table, then enter =VLOOKUP(lookup_value, table_array, col_index_num, FALSE) in a blank cell. The steps are to identify the lookup value, select a table that starts with the lookup column, count the return column, and use FALSE for an exact match.
What VLOOKUP Does
VLOOKUP means “vertical lookup.” It searches down the leftmost column of a selected range until it finds a value, then returns a value from another column in the same row. For example, you can use it to match a product code to a price, an employee ID to a department, or a customer number to an account name.
The key limitation is that VLOOKUP searches only the first column of its table range. It can return information from columns to the right, but it cannot look to the left. It also returns one result: the first matching row it finds. If a lookup key appears more than once, VLOOKUP does not combine the matches or report that the key is duplicated.
VLOOKUP is useful when your data is arranged in a conventional table and you need a straightforward lookup. Knowing its left-to-right rule and its match setting prevents most surprises.
Understand the VLOOKUP Arguments
VLOOKUP has four arguments, written in this order: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The square brackets in Excel’s function help indicate an optional argument; do not type them into a formula. For a reliable exact lookup, supply all four arguments and set the last one to FALSE.
- lookup_value: The value Excel should search for, such as a code in a cell.
- table_array: The full range containing both the lookup column and the value to return. The lookup column must be the range’s first column.
- col_index_num: The number of the column to return, counting from the left edge of table_array. The first column is 1, not 0.
- range_lookup: FALSE requests an exact match. TRUE requests an approximate match; leaving this argument out also makes Excel use approximate matching.
The column number is relative to the selected range, not the worksheet. If the table range starts in column D, then its first column still has index 1, even though the worksheet calls it column D.
Step-by-Step Exact-Match Example
Suppose a worksheet has product codes in A2:A6, product names in B2:B6, and prices in C2:C6. The rows contain P-101 / Cable / 8.50, P-102 / Adapter / 14.00, P-103 / Mouse / 19.75, P-104 / Keyboard / 42.00, and P-105 / Stand / 12.50. Cell E2 contains the code P-104. To return its price:
- Click an empty cell where you want the price to appear, such as F2.
- Start typing
=VLOOKUP(. - Use E2 as the lookup value, because that is where the requested product code is entered.
- Select A2:C6 as the table range. Column A is first, so it contains the values Excel must search.
- Enter 3 as the column index because price is the third column within A2:C6.
- Enter FALSE to require an exact code match, close the parenthesis, and press Enter.
With the described layout—codes in A2:A6, names in B2:B6, prices in C2:C6, and the requested code in E2—enter this formula in F2:
=VLOOKUP(E2,A2:C6,3,FALSE)
The result is 42, the price listed on the P-104 row. If you want the product name instead, the lookup range and lookup cell stay the same, but the return column is 2. With the same layout, this formula returns Keyboard:
=VLOOKUP(E2,A2:C6,2,FALSE)
In Microsoft 365 for Windows, you can type the formula directly into the cell or the formula bar and press Enter. Excel for Mac uses the same formula; the usual copy-and-paste shortcuts are Command+C and Command+V on Mac, compared with Ctrl+C and Ctrl+V on Windows. Excel for the web also supports VLOOKUP, though the ribbon layout and some keyboard behavior can differ from the desktop app.
Make the Formula Easier to Reuse
If you plan to fill a lookup formula down a column, lock the table range so it does not move as the formula is copied. A dollar sign before each column letter and row number makes a reference absolute. The lookup cell should usually remain relative so it changes from E2 to E3, E4, and so on as the formula is filled down.
Assuming the codes, names, and prices are still in A2:C6 and the first lookup value is in E2, this version locks the table range and returns the price:
=VLOOKUP(E2,$A$2:$C$6,3,FALSE)
When you copy it down one row, Excel adjusts E2 to E3 but keeps $A$2:$C$6 fixed. You can add the dollar signs manually or select a reference while editing and press F4 on Windows to cycle through reference types. On many Mac keyboards, the equivalent is Command+T; depending on keyboard settings, you may need to use the Fn key with a function key. The F4 key can also control screen brightness or another system feature on some laptops.
For a larger or regularly updated list, consider converting the data range into an Excel table using Insert > Table. Tables expand when new rows are added, which can help keep lookup ranges current. If you use a regular cell range, make sure newly added records are included in table_array; otherwise, VLOOKUP cannot find them.
Exact Match and Approximate Match
For IDs, names, product codes, and most everyday lookups, use FALSE. It tells Excel to find the same value rather than the nearest one. If there is no exact match, the result is generally #N/A. Leaving out the fourth argument is not the same as choosing an exact match: Excel treats an omitted range_lookup as TRUE.
TRUE is useful for threshold tables, such as a set of rate bands or grading cutoffs. In approximate mode, the first column of the lookup range must be sorted in ascending order. Excel finds the largest first-column value that is less than or equal to the lookup value. For example, suppose A2:A5 contains thresholds 0, 50, 65, and 80, and B2:B5 contains the associated labels Low, Basic, Standard, and High. With a score of 68 in E2, this formula returns Standard:
=VLOOKUP(E2,A2:B5,2,TRUE)
That result follows because 65 is the greatest threshold not above 68. If the thresholds are not sorted correctly, approximate results can be wrong. Do not use TRUE as a shortcut for an exact lookup, and do not sort data casually without understanding whether other formulas depend on its order.
Common Errors and How to Fix Them
VLOOKUP errors often come from a mismatch between the lookup value and the data, or from selecting the wrong range. Check the following before changing the formula:
- #N/A: With FALSE, Excel could not find an exact match. Check spelling, spaces, and whether one value is stored as text while the other is a number. A number formatted to look like text may still have a different underlying type.
- #REF!: The column index asks for a column beyond the right edge of table_array. Expand the range or correct the index.
- #VALUE!: Check that the column index is a valid positive number and that the arguments are in the correct order.
- A plausible but wrong result: Check whether the fourth argument is TRUE or missing, especially if the lookup column is unsorted. Use FALSE for ordinary exact lookups.
- Unexpected blanks or mismatches: Look for leading or trailing spaces, inconsistent punctuation, or numbers imported as text. Cleaning the source data may be better than trying to compensate in every formula.
VLOOKUP is not case-sensitive, so it treats uppercase and lowercase versions of otherwise identical text as matches. For exact text matching, FALSE still allows wildcard characters: an asterisk (*) can represent any number of characters, and a question mark (?) represents one character. This can be useful for intentional partial searches, but a code containing those characters may match more than you expect.
To show a friendlier message instead of #N/A, wrap the lookup in IFNA. Assuming the product table remains in A2:C6 and E2 contains the code to find, this formula returns “Code not found” if there is no exact match; otherwise, it returns the price:
=IFNA(VLOOKUP(E2,$A$2:$C$6,3,FALSE),"Code not found")
Use this only when a missing match is an expected possibility. A message can make a report easier to read, but it should not hide a data-quality problem that needs investigation.
VLOOKUP Compared with Other Lookup Options
VLOOKUP remains common because its four arguments are compact and familiar. Its main constraints are that the lookup column must be at the left edge of the selected range and that the return column is specified with a number. Inserting or deleting columns inside a range can make that number refer to a different field than intended.
XLOOKUP is often more flexible in current Excel versions: it can search in one range and return from another, including a column to the left, and it uses exact matching by default. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in older perpetual versions such as Excel 2019. INDEX and MATCH can also perform leftward lookups and are useful in workbooks that need to support older Excel releases, though their combined syntax takes more setup.
Choose VLOOKUP when the lookup field is already the first column, the table layout is stable, and readers of the workbook are familiar with it. Consider XLOOKUP or INDEX and MATCH when you need to return data from the left, avoid a numbered return column, or make the formula less sensitive to changes in table layout. Before sharing a workbook, check which Excel versions its users have.
Frequently Asked Questions
Can VLOOKUP search to the left?
No. VLOOKUP searches the first column of its table range and returns a value from a column to its right. Reorder the data, or use XLOOKUP in Microsoft 365 or Excel 2021 and later, or INDEX and MATCH if you need a leftward lookup.
Why does VLOOKUP return #N/A when the value is visible?
The displayed values may not be identical underneath. Check for extra spaces, a text-versus-number difference, or a different character such as a hyphen. Also confirm that the lookup column is the first column in the selected range and that the lookup value lies within that range.
What does the number 3 mean in VLOOKUP?
It means return the value from the third column of table_array, counting from that range’s left edge. It does not necessarily mean worksheet column C. The lookup column counts as column 1.
Should I use TRUE or FALSE?
Use FALSE for an exact match, which is the appropriate choice for most codes, names, and IDs. Use TRUE only for a deliberately approximate lookup, such as matching a score to sorted ascending thresholds.
Does VLOOKUP update when the source data changes?
Yes. A formula recalculates when the value in its source range changes, subject to the workbook’s calculation settings. However, a regular fixed range will not automatically include rows added below it; use an Excel table or update the range so new records are covered.
Before You Rely on a Lookup
Check a few returned values against the source table, especially before using the result in a report or operational workbook. Confirm that codes are unique if each code is supposed to identify one record, that the formula points to the intended columns, and that new rows are included. A lookup formula retrieves data; it does not validate whether the source record is correct or whether the key is unique.
For a basic exact lookup, the repeatable pattern is simple: place the search key in the first column of the table range, count the return column from that range’s left edge, use FALSE, and lock the range if you will copy the formula. Once those habits are in place, VLOOKUP can handle many routine spreadsheet lookups clearly and predictably.
Related
- How to Ungroup Rows in Excel With a Keyboard Shortcut
- How to Undo in Excel With a Keyboard Shortcut on Windows
- How to Undo in Excel After Saving Your Workbook Changes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- DATEDIF in Excel: Calculate Days Between Dates CorrectlyTo get the number of days between a start date in A2 and an end date in B2,…
- Excel Filter Duplicates Without Deleting Data SafelyYes. In Excel for Microsoft 365 on Windows, add a helper column that identifies repeated values, then choose…
- How to Copy in Excel Without Formulas: Keep ValuesTo copy the results of formulas without copying the formulas themselves in Excel for Microsoft 365 on Windows,…
- Concatenate Names in Excel With a Comma?To concatenate names in Excel with a comma and a space, use =TEXTJOIN(", ",TRUE,A2:A4) when the names are…
- Hide Duplicate Rows in Excel with a Filter Step by StepTo hide exact duplicate rows in Excel without deleting them, select the full data range, then choose Data…
- How to Copy an Excel Table With FormulasTo copy an Excel table and keep its formulas, select the table, press Ctrl+C, click a blank destination,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.