To apply VLOOKUP in Excel, enter =VLOOKUP(E2,A2:C6,3,FALSE) in a blank cell when the value to find is in E2, the lookup table is A2:C6, and the answer is in its third column. The final argument, FALSE, requests an exact match, which is the safest choice for most everyday lookups.
What VLOOKUP Does
VLOOKUP searches down the first column of a range for a value and returns a related value from another column in the same row. The name comes from “vertical lookup”: the search proceeds down a column rather than across a row. It is useful when a worksheet has a key such as a product code, employee ID, or customer number and you need to retrieve its description, price, or other associated detail.
For example, an order sheet might contain product codes but not product names. VLOOKUP can search a product list for each code and return the matching name. It does not move or change the source records; it displays a result in the cell containing the formula. If the source value changes, Excel recalculates the lookup result.
VLOOKUP is available in current Excel versions, including Excel for Microsoft 365. Its central limitation is important: the value being searched for must be in the leftmost column of the lookup range, and the result must be in a column to its right.
Understand the VLOOKUP Arguments
The function has four arguments in this order: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The square brackets in the function description mean the last argument is optional; they are not typed into a formula. In practice, it is usually wise to enter that argument explicitly rather than rely on the default.
- lookup_value: The value Excel should search for, such as a code in E2.
- table_array: The range containing both the search column and the return column.
- col_index_num: The position of the result column within the selected range. The first column is 1, the second is 2, and so on.
- range_lookup: FALSE requests an exact match. TRUE requests an approximate match and requires the first column to be sorted in ascending order.
The column number is relative to the selected range, not the worksheet. If the table range is B:D, column B counts as 1, C as 2, and D as 3. This detail is a frequent source of incorrect results.
Apply VLOOKUP Step by Step
Start by arranging the source information so that the values to search for occupy the leftmost column of one continuous range. Then identify the column containing the answer and count its position from the left edge of that range. In Excel for Microsoft 365 on Windows, select the result cell and type the formula directly, or use Formulas > Lookup & Reference > VLOOKUP to open the function arguments dialog.
- Choose a result cell outside the source table, such as F2.
- Enter the cell containing the value to find, followed by the table range.
- Count across the range to determine the return column number.
- Enter FALSE for an exact match in the last argument.
- Press Enter, check the returned value, and copy the formula to other rows if needed.
For instance, with the lookup code in E2, a product table in A2:C6, and prices in the third column of that range, type =VLOOKUP(E2,A2:C6,3,FALSE). Excel searches A2:A6 for the value in E2 and returns the value from column C on the matching row. In Excel for Mac and Excel for the web, the function and formula syntax are the same; the ribbon layout or placement of the function search may differ slightly.
Work Through a Concrete Example
Suppose the product table has these columns and records:
- A2:C2: P-101, Cable, 8.50
- A3:C3: P-102, Adapter, 12.00
- A4:C4: P-104, Mouse, 18.50
- A5:C5: P-105, Keyboard, 32.00
- A6:C6: P-108, Monitor, 149.00
With P-104 entered in E2, the formula =VLOOKUP(E2,A2:C6,3,FALSE) returns 18.50. That is because P-104 is found in the first column of the selected range, and the third column of that range contains its price. Changing the column index to 2 would return Mouse instead. The lookup value must match the stored code; a code with an extra space or a different underlying data type may not match as expected.
To copy the lookup down a list of orders, keep the lookup reference relative if it should advance from E2 to E3, but lock the source table range. With lookup codes in E2:E20 and the product table in A2:C6, use =VLOOKUP(E2,$A$2:$C$6,3,FALSE) in F2, then fill it down. The dollar signs keep the table range fixed while the lookup cell changes by row. On Windows, Ctrl+C and Ctrl+V copy and paste; on Mac, use Command+C and Command+V.
Choose Exact or Approximate Match
For product codes, names, account IDs, and most other identifiers, use FALSE. An exact lookup either finds the matching value or returns an error; it will not silently substitute a nearby code. FALSE is not the default in every usage pattern, so writing it explicitly makes the formula’s intention clear.
TRUE is intended for ordered thresholds, such as commission bands, grade boundaries, or shipping brackets. The first column of the lookup range must be sorted from smallest to largest. VLOOKUP then finds an exact value if available; otherwise, it uses the largest value that is less than the lookup value. For a score table with boundaries 0, 60, 75, and 90 in A2:A5 and labels in B2:B5, with a score of 82 in E2, =VLOOKUP(E2,A2:B5,2,TRUE) returns the label beside 75, because 75 is the greatest boundary not exceeding 82. If the threshold list is unsorted, an approximate lookup can return an unreliable result.
A common misconception is that TRUE simply means “find something close.” It follows a specific ordered-boundary rule, not a general similarity search. Do not use it for IDs or text labels where a near match has no useful meaning.
Common Errors and How to Fix Them
#N/A means an exact match was not found, or the lookup value and source value differ in a way that prevents matching. Check spelling, leading or trailing spaces, number-versus-text formatting, and whether the key is actually in the first column of the selected range. A code stored as text is not always equivalent to the same-looking number stored as a numeric value.
#REF! commonly appears when the column index asks for a column beyond the selected range. If the range is A:C, valid column indexes are 1, 2, and 3. #VALUE! can indicate an invalid argument, including a column index below 1. If the formula returns a plausible but wrong value, check the column count and whether the final argument is TRUE when you intended FALSE.
For a user-friendly message when a lookup has no match, wrap the exact-match formula in IFERROR. With the lookup value in E2 and the table in A2:C6, enter =IFERROR(VLOOKUP(E2,A2:C6,3,FALSE),"Not found"). This displays Not found for any error, not only a missing match, so it can also conceal another issue such as an invalid column number. During troubleshooting, inspect the underlying formula and source data rather than automatically hiding every error.
Keep the Lookup Range Reliable
A fixed cell range works well for a small, stable list, but it can become inconvenient when records are added. If new rows fall outside A2:C6, a formula that still refers to A2:C6 will not search them. One way to reduce that risk is to turn the source data into an Excel table: select a cell in the data and choose Insert > Table, confirm the range and whether it has headers, and give the table a meaningful name from the Table Design tab.
With a table named Inventory whose columns are SKU, Product, and Price, and the lookup SKU in E2, =VLOOKUP(E2,Inventory,3,FALSE) searches the table’s first column and returns its third column. Table references can expand as rows are added, making maintenance easier. However, the same leftmost-column rule still applies, and the numeric column index still depends on the order of the table’s columns.
When a result is copied across multiple columns, the column index can be made flexible with a separate calculation, but that adds complexity. For a simple worksheet, a clearly documented fixed index is often easier to audit. Add headers to the source table, use consistent data types, and avoid merged cells or blank rows inside the lookup data.
VLOOKUP Compared with Other Lookup Options
VLOOKUP remains a practical choice when the search key is the first column and the answer is to its right. It is familiar and works in many older Excel files. It is less suitable if you need to retrieve information from a column to the left of the key, or if inserting or rearranging columns might make a hard-coded column index point to the wrong field.
XLOOKUP is a newer alternative in Microsoft 365 and Excel 2021 or later. It can search in one range and return a result from another, including a column to the left, and it has a built-in option for a custom not-found result. It is not available in some older Excel versions, so a workbook shared with people using older software may need a compatible formula such as VLOOKUP. Choose based on the workbook’s audience and structure, not just on which function is newest.
For a two-way lookup, where both a row key and a column heading determine the answer, a combination of INDEX and MATCH or newer dynamic lookup functions may be more appropriate. VLOOKUP is designed for a single search column and a return column on the same row; it does not by itself choose a return column based on a heading’s text.
Frequently Asked Questions
Why does VLOOKUP return #N/A when the value appears to be there?
Check for hidden spaces, different data types, or a mismatch in punctuation. Confirm that the key is in the first column of the selected range and that the formula uses the intended lookup value. Exact match does not ignore differences just because two entries look similar on screen.
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. Rearrange the source data, use XLOOKUP in a supported version, or use another lookup approach if the required result is left of the key.
Do I have to sort the data for VLOOKUP?
Not when you use FALSE for an exact match. Sorting is required for a reliable approximate match with TRUE, where the first column must be in ascending order. Sorting an exact-match list may help people read it, but it is not required for the lookup itself.
What does the number 3 mean in a VLOOKUP formula?
It tells Excel to return the value in the third column of the selected table range. It does not mean worksheet column C in every formula: the count starts at the left edge of the range you supplied.
Can I use VLOOKUP with text as well as numbers?
Yes. It can look up text, numbers, dates, and other values, provided the source and lookup values are compatible and the key is in the first column. For text matching, VLOOKUP is not case-sensitive, so uppercase and lowercase differences alone do not distinguish two keys.
Related
- Excel Attendance Format: A Clear Monthly Attendance Tracker
- Excel Attendance File to Print: Set Up and Print It
- Excel Timesheet Format: Build a Reliable Weekly Sheet
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula to Combine Text From Two Cells with a SpaceWith text in A1 and B1, join the contents directly with =A1&B1; to put a space between them,…
- Split Names in Excel: Text to Columns and FormulasTo split names into separate Excel cells, select the name column and choose Data > Text to Columns…
- Excel ROUND DOWN to Whole Number: Formulas and ExamplesTo round a number down to a whole number in Excel, use ROUNDDOWN with zero decimal places. If…
- Excel Data Analysis Books: How to Choose the Right OneGood Excel data-analysis books include Microsoft Excel Data Analysis and Business Modeling by Wayne L. Winston for business…
- Microsoft Excel Lookup Function Examples That WorkFor most exact-match lookups in Microsoft Excel for Microsoft 365, use XLOOKUP: with product codes in A2:A6, prices…
- Excel Formula for Name Arrangement: Sort Names A to ZFor Microsoft 365, use =SORT(A2:A20) to arrange a list of names alphabetically; replace the range with your own.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.