With a lookup value in E2 and a table whose IDs are in A2:A5 and prices are in C2:C5, enter =VLOOKUP(E2,$A$2:$C$5,3,FALSE) to return the matching price. For the sample below, entering P-318 returns 2.40; the final FALSE requests an exact match.
A Basic VLOOKUP Excel Sample
Imagine a small product list in columns A through C, with each product ID appearing once. The first column of the lookup range must contain the values Excel will search for.
| Cell range | Product ID | Product | Price |
|---|---|---|---|
| Row 1 | Product ID | Product | Price |
| Row 2 | P-104 | Notebook | 3.50 |
| Row 3 | P-205 | Pen | 1.25 |
| Row 4 | P-318 | Folder | 2.40 |
| Row 5 | P-422 | Marker | 1.80 |
Put the ID you want to find, P-318, in E2. The formula shown in the opening paragraph searches column A, finds that ID on row 4 of the table, and returns the value from the third column of the selected range: 2.40. If the result displays as 2.4, the number is still the same; apply a two-decimal number format if you want it to appear as 2.40.
To return the product name instead, keep the same lookup value and table range but request column 2. With the product table still in A2:C5 and the ID in E2, enter =VLOOKUP(E2,$A$2:$C$5,2,FALSE). For P-318, the result is Folder. The column number is counted from the left edge of the selected range, not from the worksheet’s column letters.
How the VLOOKUP Arguments Work
VLOOKUP has four arguments: the value to find, the table range, the number of the return column, and the match type. In =VLOOKUP(E2,$A$2:$C$5,3,FALSE), E2 is the lookup value, $A$2:$C$5 is the table array, 3 is the return-column index, and FALSE means an exact match.
- Lookup value: This can be a cell reference such as E2 or a typed value. A reference is usually easier to update and reuse.
- Table array: The leftmost column of this range is where VLOOKUP searches. The return value must be in a column to its right.
- Column index: Count columns within the chosen table range, starting at 1. In A:C, column A is 1, B is 2, and C is 3.
- Range lookup: FALSE asks for an exact match. TRUE asks for an approximate match; if this argument is omitted, Excel uses approximate matching.
The dollar signs in $A$2:$C$5 make the range absolute. If you copy the formula down a column, the lookup reference E2 can become E3, while the source table stays in place. This matters when applying one lookup formula to a list of IDs.
Set Up the Lookup Table Carefully
A dependable result starts with a well-structured table. Put the key you will search for in its leftmost column, keep each record on one row, and place the value you want returned in a column to the right. VLOOKUP cannot search a column to the right and return a value from a column to its left. If your source data is arranged the other way around, rearrange the columns or consider a different lookup method.
Check the lookup values for consistency. An ID stored as text may not match an apparently identical ID stored as a number. Extra spaces can also cause an exact match to fail. For example, a product code with a trailing space is not necessarily identical to the same code without one. Cleaning or standardizing imported data can prevent confusing results.
For a growing list, convert the source range into an Excel table using Insert > Table, or Home > Format as Table. Tables expand when you add rows, reducing the chance that a new record will fall outside a fixed range. In a workbook shared with others, descriptive column headings also make it easier to verify that the return-column number points to the intended field.
Exact Match or Approximate Match?
For product codes, employee IDs, invoice numbers, and most other identifiers, use FALSE. An exact lookup either finds the requested value or reports that it cannot find it. It will not quietly return a nearby code. This is the safest default for many everyday lookup tasks.
TRUE is useful for ranges and thresholds, such as assigning a rate based on sales, a grade based on a score, or a shipping band based on weight. For approximate matching to work correctly, the first column of the table must be sorted in ascending order. Excel returns the row for an exact value if present; otherwise, it uses the largest first-column value that is less than or equal to the lookup value. Values below the smallest threshold have no valid lower band.
For example, suppose A10:B13 contains thresholds and rates: 0 and 0%, 1,000 and 5%, 5,000 and 8%, and 10,000 and 10%. With the sales amount 6,200 in D2 and those thresholds sorted ascending in A10:B13, enter =VLOOKUP(D2,$A$10:$B$13,2,TRUE). It returns 8%, because 5,000 is the largest threshold not greater than 6,200. If the thresholds are unsorted, the result may be wrong, even if the formula does not show an error. Use FALSE rather than TRUE when your task requires an exact identifier match.
Common VLOOKUP Problems and Fixes
#N/A usually means an exact value was not found. Check for a typo, hidden spaces, inconsistent number-versus-text formatting, or a lookup value missing from the first column of the range. Also confirm that the formula is searching the correct column. If a not-found result is expected and you want a friendlier message, IFNA can handle that case. With the product table in A2:C5 and the lookup ID in E2, enter =IFNA(VLOOKUP(E2,$A$2:$C$5,3,FALSE),"Not found"). For an absent ID such as P-999, it returns Not found; for P-318, it still returns 2.40.
#REF! commonly appears when the column index is larger than the number of columns in the selected table range, or when a referenced column has been deleted. Count the columns from the left edge of the range and make sure the requested index is valid. For instance, a three-column range only has valid return-column numbers 1, 2, and 3.
Wrong result without an error: Check whether the last argument is TRUE or missing when you intended an exact match. Approximate matching requires the first lookup column to be sorted. Also confirm that the column index selects the right field; choosing 2 rather than 3 may return a valid but unintended value. Finally, if several rows contain the same lookup value, VLOOKUP returns the first match it encounters, so duplicate keys can hide later records.
When debugging, temporarily inspect the source row and compare the lookup value to its key character by character. Avoid wrapping every lookup in IFERROR without understanding the cause: that function can hide problems beyond a missing match, such as a broken reference. IFNA is more targeted when the only error you mean to replace is #N/A.
Copying Formulas and Keeping Results Stable
To fill a lookup down a worksheet, use a relative reference for the changing lookup cell and absolute references for a fixed source range. For example, if IDs are in E2:E20 and the product table remains in A2:C5, the formula in F2 can use E2 and $A$2:$C$5. Copying it down changes E2 to E3, E4, and so on, without shifting the table. If you add records below row 5, update the range or use an Excel table so those records are included.
VLOOKUP returns a value from the matched row; it does not create a permanent link between two records. If the source price changes, a recalculated formula can show the new price. If you need a fixed snapshot, copy the results and use Paste Special > Values. Keep the original formulas or a separate copy of the source data if you may need to audit how those values were produced.
VLOOKUP Compared with XLOOKUP
VLOOKUP remains useful in existing workbooks and in files that need to work with older Excel releases. Its main constraints are that the lookup column must be at the left of the table range and the return column is identified by a number. Inserting or rearranging columns can make a hard-coded column index point to a different field.
XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It can look in one range and return a value from another, including a column to the left, and it has an optional not-found result. For a new workbook in a supported version, it may be simpler to read and maintain. VLOOKUP is still a reasonable choice when a sample or shared workbook needs the familiar four-argument pattern, or when compatibility with older Excel matters. The formulas themselves are generally the same in Excel for Windows, Mac, and the web, though regional settings can affect whether formulas use commas or semicolons as separators.
Frequently Asked Questions
What is a simple VLOOKUP formula in Excel?
With the lookup value in E2 and a table in A2:C5 whose keys are in column A, use =VLOOKUP(E2,$A$2:$C$5,3,FALSE) to return the matching value from the third table column. Replace 3 with the position of the field you need.
What does FALSE mean in VLOOKUP?
FALSE tells Excel to look for an exact match. If the lookup value is absent, the formula normally returns #N/A rather than choosing a nearby value. Use it for identifiers and other values that must match precisely.
Why does VLOOKUP return #N/A when the value is there?
The displayed values may look alike but differ because one is text and the other is a number, or because one contains extra spaces or a different character. Check that VLOOKUP searches the correct leftmost column and that the exact value appears in its lookup range.
Can VLOOKUP return a value from a column on the left?
No. Its search column must be the first column in the selected range, and its return column must be to the right. Rearrange the source columns or use XLOOKUP in Microsoft 365 or Excel 2021 and later if you need to look left.
Do I need to sort data for VLOOKUP?
Not when the fourth argument is FALSE for an exact match. With TRUE or an omitted fourth argument, the first column must be sorted in ascending order for approximate matching to return the expected threshold band.
Why does my VLOOKUP show the wrong column?
The third argument counts columns from the start of the table range, not from worksheet column A. Recount the selected range and verify that the index identifies the field you want. Also check for duplicate lookup keys, since VLOOKUP returns the first matching row.
Related
- Excel Data Analysis Toolkit: Setup, Tools, and Practical Uses
- Excel Data Analysis Sampling: Methods, Steps, and Uses
- Excel Data Analysis Skills: What They Include and Why
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel TRIM Formula Examples for Cleaning Messy TextIn Excel for Microsoft 365, use =TRIM(A2) to remove leading and trailing ordinary spaces from the text in…
- Excel Function to Keep a Cell Constant in Formulas ExplainedTo keep a cell reference fixed when you copy an Excel formula, make it absolute by adding dollar…
- DAX Formula to Concatenate Text in Power BIIn Power BI, use the DAX & operator to join text: Customers[FirstName] & " " & Customers[LastName]. Use…
- Excel How to Filter Columns: Steps and TipsTo filter columns in Excel for Microsoft 365 on Windows, select a cell in your data, choose Data…
- Concat Excel Different Sheets: Formulas and ExamplesTo concatenate values from different Excel sheets, use CONCAT with a reference to each sheet, such as =CONCAT(Jan!A2,Feb!A2).…
- Excel Number to Text: Formulas, Limits, and OptionsTo turn a number into formatted text in Excel, use the TEXT function: with a number in A2,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.