For a reliable VLOOKUP in Excel, put the value you want to find in the first column of the lookup range, use FALSE for an exact match, and lock the range with dollar signs if you will copy the formula. For example, with product IDs in A2:A5, names in B2:B5, prices in C2:C5, and the ID to find in E2, enter =VLOOKUP(E2,$A$2:$C$5,3,FALSE) to return the matching price.
What VLOOKUP does and how it works
VLOOKUP means vertical lookup: Excel searches down the first column of a selected table range for a value, then returns a value from another column on the same row. It is useful when a worksheet has a key such as an employee ID, product code, or postal code, and you need to retrieve related information from a table.
The function has four arguments: the value to find, the table range, the number of the return column within that range, and the match mode. Column numbering starts at 1 for the range’s first column, not at the worksheet’s column A. In the example above, the range begins in A, so price in C is column 3. If the range instead began in B, column C would be number 2.
VLOOKUP returns a value from the same row as the match. It cannot return a result from a column to the left of the lookup column. That limitation matters when arranging a source table: the key must be at the left edge of the range you give the function. A lookup range can include extra columns, but the column number must point to the result field you actually want.
Choose the right match mode
For most everyday lookups, use FALSE as the fourth argument. It tells Excel to find an exact match. If you omit this argument, VLOOKUP uses approximate matching, which can return a plausible but incorrect result when the first column is not sorted as required.
- Exact match: Use
FALSEfor identifiers, names, product codes, and other values that should match precisely. If Excel cannot find the value, it returns#N/A. - Approximate match: Use
TRUE, or omit the fourth argument, for sorted numeric breakpoints such as grading bands or commission thresholds. The first column must be sorted in ascending order. Excel returns the row for an exact match or, otherwise, the largest value that is less than the lookup value.
For instance, a rate table might list lower limits of 0, 100, and 500 in ascending order. An approximate lookup for 240 can return the band beginning at 100. This method is not a shortcut for ordinary exact lookups: an unsorted table can produce an unexpected answer, and an input smaller than the first breakpoint may return an error. If you are unsure which mode suits the task, choose FALSE.
Build a formula and copy it safely
Suppose a small product list has IDs in A2:A5, descriptions in B2:B5, and prices in C2:C5. E2 contains P-204, and that item costs $18.50. The formula below asks Excel to search the first column of A2:C5 and return the third column. The dollar signs keep the table range fixed when the formula is copied to other rows.
With product IDs in A2:A5, descriptions in B2:B5, prices in C2:C5, and P-204 in E2, enter: =VLOOKUP(E2,$A$2:$C$5,3,FALSE). The result is 18.50 if P-204 is on the table row with that price.
In Microsoft 365 for Windows, press F4 while editing a selected cell reference to cycle through relative and absolute forms. For example, selecting A2:C5 and pressing F4 changes it to $A$2:$C$5. In Excel for Mac, Command+T cycles the reference style; the exact function is the same. You can also type the dollar signs directly. A fixed range is especially important when you fill a formula down, because a relative range would shift from A2:C5 to A3:C6 and could omit the first row of your data.
To fill a formula down in Windows, select the formula cell and drag its fill handle, or copy and paste it. On Mac, use the same fill handle or Command+C and Command+V. The lookup cell reference E2 should usually remain relative so it becomes E3, E4, and so on, while the table range remains absolute. Avoid locking E2 unless every row is meant to search for the same input.
Prepare the lookup data
Many lookup errors come from inconsistent source data rather than a faulty function. Check that the key column contains one consistent type of value. A number stored as text may not match the same digits stored as a number. Leading or trailing spaces can also prevent a text value from matching; a value that looks like ABC may actually contain an invisible space.
- Compare representative values in the lookup cell and source key column. Check whether they are numbers, text, or dates, and whether their formatting or spelling differs.
- Remove unintended spaces and standardize codes before relying on a large lookup. Be cautious with leading zeros: an ID such as 0042 may need to remain text rather than become the number 42.
- Check for duplicate keys. With an exact match, VLOOKUP returns the first matching row from the top of the range, so duplicate IDs can hide later records.
- Keep the key column inside the selected range and make sure the return column is included. A column inserted or deleted in the source table can change what a hard-coded column number returns.
Formatting alone does not necessarily change the underlying value. For example, applying a date format to a number does not turn an unrelated text date into the same stored value. When data comes from exported systems, cleaning and standardizing the key fields can be more reliable than repeatedly adjusting the lookup formula.
Handle missing values and common errors
#N/A usually means that an exact match was not found, but it can also reveal a text-versus-number mismatch, an extra space, or an incomplete lookup range. Check the lookup value and the first column of the range before masking the error. #REF! can occur if the column index points beyond the selected range, or if a referenced column has been removed. A wrong but error-free answer often comes from using TRUE accidentally, choosing the wrong column number, or searching a table with duplicate keys.
If a missing key is an expected possibility, you can replace the visible error with a useful message. In this example, A2:A5 contains IDs, B2:B5 contains descriptions, and E2 contains the ID being searched. The formula returns the description for a match, or Not found when Excel returns an error such as #N/A.
With IDs in A2:A5, descriptions in B2:B5, and the search value in E2, enter: =IFERROR(VLOOKUP(E2,$A$2:$B$5,2,FALSE),"Not found"). If E2 is absent from the ID list, the displayed result is Not found.
IFERROR handles more than a missing match, so it can also conceal a broken range or other formula problem. During setup, it is often useful to inspect the underlying error first. Once the formula is verified, use a message that helps the reader understand what happened rather than replacing every possible issue with a blank cell.
Use wildcards and understand their limits
VLOOKUP with exact-match mode can use wildcard characters when the lookup value is text. An asterisk (*) represents any number of characters, and a question mark (?) represents one character. A tilde (~) escapes a wildcard when you need to search for the character itself. Wildcards can help when matching a known part of a text entry, but they are not appropriate for unique identifiers if partial matches could point to the wrong record.
For example, searching for a value beginning with a known prefix can match several rows. VLOOKUP returns the first matching row, not a list of all matches. If the data contains multiple possible results, add a more specific key or use a function designed to return multiple rows. Also remember that exact matching is not case-sensitive: codes that differ only by uppercase and lowercase letters are not treated as distinct by ordinary VLOOKUP matching.
VLOOKUP compared with XLOOKUP
XLOOKUP is a newer alternative in Microsoft 365 and Excel 2021 or later. Unlike VLOOKUP, it can search one range and return a result from a separate range to its left or right, so the lookup key does not have to be the first column in a wider table. It also uses exact match by default and offers an argument for a custom result when nothing is found.
VLOOKUP remains useful when a workbook must work in older Excel versions, when a team already understands the formula, or when the source table is designed with its key at the left. It can also be easier to read in simple, fixed tables. For a new workbook that will only be opened in supported newer versions, XLOOKUP may be more flexible; for shared files, confirm which Excel versions recipients use before choosing a newer function.
Another option is INDEX with MATCH, which can look left or right and is supported in older Excel versions. It uses two functions and can be less immediately readable to occasional users, but avoids VLOOKUP’s fixed return-column number. Choosing between these approaches depends on compatibility, table layout, and whether the people maintaining the workbook can understand and troubleshoot the formula.
Frequently Asked Questions
Why does VLOOKUP return #N/A when the value is there?
Check for spaces, text-versus-number differences, and whether the lookup value is actually in the first column of the selected range. Confirm that the formula uses the correct range and FALSE for an exact match.
Why does VLOOKUP return the wrong column?
The third argument counts columns from the left edge of the table range, starting at 1. Recount the range columns, and check whether someone inserted or deleted a source column after the formula was written.
Does VLOOKUP look to the left?
No. It searches the first column of its table range and returns a value from a column to the right. Rearrange the table, use INDEX with MATCH, or use XLOOKUP in Excel 2021 or Microsoft 365 if the result needs to come from the left.
Do I need to sort the table for VLOOKUP?
Not for an exact match using FALSE. For approximate matching with TRUE or an omitted fourth argument, the first column must be in ascending order for dependable results.
Can VLOOKUP return more than one result?
A single VLOOKUP formula returns a value from one matching row, and duplicate keys yield the first match. To retrieve multiple matching rows, use a suitable alternative such as FILTER in Microsoft 365 or Excel 2021, subject to the workbook’s compatibility needs.
A final checklist for dependable lookups
Before using a VLOOKUP result in a report or calculation, verify the key, the table boundaries, the return-column number, and the match mode. Test at least one known match and one value that should be missing. If the result will be copied, make the table range absolute and confirm that the lookup reference changes as intended. For approximate matching, check the sort order and test values around each threshold. These small checks catch many errors before they spread through a workbook.
Related
- Excel Redo Shortcut: How to Restore an Undone Action
- Redo in Excel: Shortcut, Limits, and Common Problems
- Redo in Excel: Restore an Undone Action
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Age From DOB: Years, Months, and DaysTo calculate age in completed years from a date of birth in cell A2, use =DATEDIF(A2,TODAY(),"Y"). It compares…
- LEFT and LEN Excel Formula: How to Use Them TogetherTo remove the last character from text in Excel, combine LEFT and LEN like this: =LEFT(A2,LEN(A2)-1). With the…
- Percent Change Calculator Excel Formula and ExamplesIn Excel, calculate percent change as (new value − old value) ÷ old value. If the old value…
- Excel Function to Remove Text from a StringExcel does not have one universal “remove text” function: use SUBSTITUTE to remove specified characters or words, REPLACE…
- INDEX Excel Arguments Explained: Syntax and ExamplesIn Excel, INDEX returns a value or reference at the intersection of a specified row and column in…
- Excel Cell Fill Shortcut: Fill Down, Fill Right, and ColorIn Excel for Microsoft 365 on Windows, press Ctrl+D to fill down from the top cell of a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.