If VLOOKUP is not working correctly, first check that the lookup value is in the leftmost column of the selected range, use the correct return-column number, and specify an exact match. With lookup IDs in A2:A5, return values in B2:B5, and the ID to find in D2, use =VLOOKUP(D2,$A$2:$B$5,2,FALSE); this returns the value in column B for an exact match.
How VLOOKUP searches a table
VLOOKUP searches down the first column of a rectangular range, finds a matching value, and returns a value from another column in the same row. Its name means “vertical lookup.” In the example above, the search happens in column A, and the number 2 tells Excel to return the second column of the selected range, which is column B. The dollar signs keep the range fixed if you copy the formula to other cells.
The function’s arguments are, in order: the value to find, the table range, the number of the column to return, and whether to use an exact or approximate match. That last argument matters especially. FALSE requests an exact match; TRUE requests an approximate match. If you leave the final argument out, Excel uses approximate matching, which is a frequent cause of unexpected results.
For example, if D2 contains the ID 104 and A3 contains 104, the formula returns the value from B3. It does not search for 104 anywhere in the selected table: the lookup column must be the range’s first column. If the IDs are in B and the values to return are in A, ordinary VLOOKUP cannot search left from B to A.
Check the match setting first
For names, product codes, invoice numbers, and most ordinary lookups, use FALSE or 0 as the final argument. Exact match means Excel returns a result only when it finds the lookup value. If there is no match, the usual result is #N/A. This is generally the safest setting because it does not depend on the lookup column being sorted.
Approximate match is useful for bands and thresholds, such as assigning a category based on a score or rate. It is not a “close enough” search that picks the most similar text. It finds an exact value when available or, otherwise, the nearest lower value in a lookup column sorted in ascending order. An unsorted column can therefore produce a plausible-looking but incorrect answer.
With thresholds in A2:A4 sorted from lowest to highest—0 for Bronze, 100 for Silver, and 500 for Gold—and the score 275 in D2, this formula returns Silver:
With those threshold labels in B2:B4, use =VLOOKUP(D2,$A$2:$B$4,2,TRUE). A score of 275 falls between 100 and 500, so the result comes from the row beginning at 100. Confirm that the lookup column is sorted in ascending order before relying on this kind of formula.
Resolve #N/A and values that look identical
#N/A means Excel did not find a match under the formula’s rules. Before changing the formula, compare the lookup value and the corresponding table entry carefully. Two cells can display the same characters while holding different underlying values. Common examples include a number stored as text, a hidden leading or trailing space, and a nonbreaking space copied from a website.
- Check the data type: A numeric 104 and text “104” are not always interchangeable in an exact lookup. Select the cells and inspect their alignment, number format, or formula bar; formatting alone does not convert text into a number.
- Inspect spaces and characters: A trailing space in “AB12 ” prevents an exact match with “AB12.” Use Excel’s Find and Replace or clean the source data. TRIM removes ordinary extra spaces, but it does not remove every special whitespace character.
- Check for inconsistent codes: Look for hyphens, punctuation, leading zeros, and accidental changes in capitalization or spelling. For text lookups, VLOOKUP is generally not case-sensitive, but the other characters still need to match.
- Confirm the value is in the lookup column: Search the first column of the selected range, not simply somewhere in the worksheet.
When the lookup value is numeric text but the first column contains real numbers, and D2 contains a numeric-looking value, you can convert the lookup value to a number. With the numeric keys in A2:A5 and results in B2:B5, =VLOOKUP(VALUE(D2),$A$2:$B$5,2,FALSE) attempts that conversion before searching. VALUE returns an error if D2 cannot be interpreted as a number, so it is not a universal cleanup method. If the IDs are codes where leading zeros matter, keep them consistently stored as text instead of converting them to numbers.
Fix the table range and return-column number
The third argument is a position within the selected range, not a worksheet column letter. If the range is A2:C20, then A is column 1, B is column 2, and C is column 3. A column number of 4 would be outside that range and produce #REF!. If the formula returns a value from the wrong field, count the columns beginning at the left edge of the range.
A common copying problem occurs when the table range shifts as a formula is filled down. In =VLOOKUP(D2,A2:B5,2,FALSE), Excel may change the range to A3:B6 when you copy the formula down one row. Locking the range as $A$2:$B$5 keeps it in place, while leaving D2 relative allows the lookup value to advance to D3, D4, and so on. You can also convert a data range into an Excel table so that references can expand as rows are added.
VLOOKUP also returns the first matching row it encounters. If the lookup column contains duplicate IDs with different results, the function will not report a duplicate warning or choose the most recent record automatically. Remove or resolve duplicates, or add a more specific key, if each lookup should identify one unique row.
Understand #REF!, #VALUE!, and wrong-looking results
#REF! often indicates that the return-column number is greater than the number of columns in the selected range, or that a referenced cell or range was deleted. Recheck both the range and the third argument. #VALUE! can appear when an argument has an invalid type or the column number is not usable; confirm that the column number is a positive integer within the table range.
A wrong-looking result can be harder to spot than an error. Approximate match on unsorted data, duplicate keys, a range that moved, or a return-column number aimed at the wrong field can all return a value without displaying an error. Trace the formula’s references, verify the match mode, and compare the returned row with the source table. In Excel for Microsoft 365 on Windows, select the formula cell and use Formulas > Evaluate Formula to step through how Excel evaluates it.
You may choose to replace a missing-match error with a readable message. With IDs in A2:A5, results in B2:B5, and the requested ID in D2, =IFERROR(VLOOKUP(D2,$A$2:$B$5,2,FALSE),"Not found") displays Not found when the lookup produces an error. This makes a report easier to read, but it can also conceal a broken range or a data-type problem. Use it after checking the underlying lookup, rather than as a substitute for troubleshooting.
When VLOOKUP is not the best choice
VLOOKUP is convenient when the lookup column is on the left and the result is to its right. Its fixed column number can become fragile if someone inserts or rearranges columns within the table. If you have Excel for Microsoft 365, XLOOKUP can search one range and return a corresponding value from another, including a column to the left. It also supports an optional not-found message.
With IDs in A2:A5, results in B2:B5, and the requested ID in D2, =XLOOKUP(D2,A2:A5,B2:B5,"Not found") searches the ID range and returns the corresponding result, or Not found if there is no match. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in older perpetual Excel releases such as Excel 2019. For older versions, INDEX and MATCH can provide a flexible alternative, though the formulas require more setup.
Switching functions will not automatically fix inconsistent source data. XLOOKUP also needs compatible values to match, and duplicate keys still require a decision about which record should be returned. First identify whether the problem is the function’s left-to-right layout, an incorrect formula, or dirty data; then choose the solution that addresses that cause.
A practical troubleshooting order
Work through the checks in this order so that you change one thing at a time:
- Read the formula from left to right and identify the lookup value, table range, return-column number, and match setting.
- Make sure the value to find is in the first column of the selected range and the return-column number points to the intended field.
- Set the final argument to FALSE for an exact lookup unless you deliberately need sorted thresholds.
- Check whether the target value exists in the first column, and compare its type, spaces, punctuation, and leading zeros with the lookup value.
- Lock a range that should not move when copied, then check for duplicate keys and confirm that the chosen return row is the right one.
- After correcting the cause, fill or copy the formula again and verify a few results against the source table.
This sequence helps distinguish a formula error from a data problem. For instance, if the formula structure is right but one imported product code returns #N/A, inspect that code and its source cell before rewriting every lookup in the workbook.
Frequently Asked Questions
Why does VLOOKUP return #N/A when the value is present?
Often, the visible values are not identical internally: one may be text and the other a number, or one may contain an extra space or character. Also confirm that the value appears in the first column of the selected range and that the formula uses the intended exact-match setting.
Why does VLOOKUP return the wrong result instead of an error?
Check whether the formula uses TRUE or omits the final argument, which invokes approximate matching. Unsorted thresholds can return an unexpected row. Also check the return-column number, moving range references, and duplicate keys, because each can produce a valid-looking result from the wrong row or field.
Does VLOOKUP search to the left?
No. It searches the first column of its table range and returns data from a column to the right. Use XLOOKUP in supported Excel versions, or an INDEX-and-MATCH approach in versions that lack XLOOKUP, when the return column is on the left.
Is VLOOKUP case-sensitive?
No, ordinary VLOOKUP text matching is not case-sensitive, so “north” and “North” are treated as matches. If uppercase and lowercase letters must be distinguished, use a case-sensitive approach rather than relying on VLOOKUP alone.
Why does my VLOOKUP formula change when I copy it?
Relative references adjust as a formula is copied. Add dollar signs to the table range, such as $A$2:$B$5, to keep it fixed, while leaving the lookup-cell reference relative if it should change on each row. In Windows Excel, F4 cycles through reference types while editing or selecting a reference; on Mac, the shortcut can depend on keyboard settings, so entering the dollar signs directly is a reliable option.
Related
- Excel Data Validation Shortcut: Windows and Mac
- Data Validation Plan in Excel: Build Reliable Checks
- Excel Data Validation: Select Multiple Items from a List
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Scatter Plot: Connect Dots, Choose Lines, Fix IssuesYes. To connect points in an Excel scatter plot, choose a scatter chart type with lines: select Insert…
- How to Fix Double Spaces Between Words in Microsoft WordTo fix double spaces in Word, open Home > Replace, enter two spaces in Find what and one…
- Excel OLE Action Error: What It Means and How to Fix ItThe Excel OLE action error means Excel is waiting for another program to respond, usually an app or…
- Excel File Won’t Save? Fix the Cause and Recover WorkIf an Excel file won’t save, first use File > Save As to try saving a copy to…
- Excel Dropdown List Not Showing? Fix the ArrowIf an Excel dropdown list is not showing, select the cell and check Data > Data Validation: make…
- Excel Circular Reference Popup: How to Find and Fix ItThe Excel circular reference popup means a formula, directly or through other cells, depends on its own result.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.