Fix VLOOKUP Not Working: Causes and Solutions

To fix VLOOKUP in Microsoft Excel for Microsoft 365, check that the lookup value is in the first column of the selected range, use the correct column number, and set the final argument to FALSE for an exact match. If the result is still wrong or missing, check for text-versus-number mismatches, extra spaces, an incorrect range, or an error such as #N/A or #REF!; the sections below explain how to identify and correct each cause.

How VLOOKUP works

VLOOKUP searches down the first column of a range for a value, then returns a value from another column in the same row. Its arguments are the value to find, the table range, the number of the result column counted from the range’s left edge, and an optional match setting. In a lookup for an employee name by ID, for example, the IDs must be in the first column of the selected range and the names must be in a column to their right.

For ordinary lists, the match setting is the most important detail. FALSE requests an exact match; TRUE, or leaving the final argument out, requests an approximate match. Approximate matching assumes that the first column is sorted in ascending order. If it is not, the result can be incorrect without an obvious error message. Use FALSE unless you deliberately need approximate matching for a sorted range, such as a set of score bands.

VLOOKUP only searches the leftmost column of its selected range and returns data to the right. It also returns the first matching row when duplicate lookup values exist. These are built-in limits, not signs that the formula is broken.

Start with a reliable exact-match formula

With product codes in A2:A4, product names in B2:B4, and the code to search for in D2, enter this formula in E2: =VLOOKUP(D2,$A$2:$B$4,2,FALSE). If A2:A4 contains 104, 205, and 317, B2:B4 contains Cedar, Mira, and Sol, and D2 contains 205, the result is Mira. The 2 means “return the second column of the selected range,” and the dollar signs keep the range fixed if you copy the formula down.

Check the pieces one at a time if it does not work:

Confirm that D2 contains the value you intend to search for.
Confirm that the lookup values are in the first column of A2:B4.
Count the result column from the range’s left edge. In A2:B4, column A is 1 and column B is 2.
Use FALSE for an exact match, especially for names, IDs, and product codes.
Make sure the formula’s range includes every row that might contain the match.

When copying a formula down, a relative range such as A2:B4 shifts to A3:B5, which can make later results disappear or change. The dollar signs in $A$2:$B$4 make that reference absolute. Alternatively, convert the data to an Excel table so its range can expand as rows are added; select the data and choose Insert > Table.

Fix #N/A and missing matches

#N/A means VLOOKUP could not find a matching value under the formula’s rules. First check the source data and the lookup cell visually, but do not assume that values that look alike are identical. A code stored as the number 205 is not always treated as the same value as the text “205.” Leading or trailing spaces can also prevent a match. This commonly happens when data is copied from a website, a report, or another system.

With lookup values in A2:A100 and the search value in D2, this diagnostic formula counts exact matches: =COUNTIF($A$2:$A$100,D2). If D2 contains 205 and that value appears once in the lookup range, the result is 1; a result of 0 suggests that the value is absent or stored differently. COUNTIF supports wildcard characters in text criteria, so interpret its result cautiously when the search value itself contains an asterisk or question mark.

To clean ordinary extra spaces and nonprinting characters, with the unclean value in A2 enter this in B2: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))). For example, a value with a nonbreaking space before or after AB-17 becomes AB-17. Fill the helper formula down, then use the cleaned column as the lookup column. CLEAN removes certain nonprinting characters, while SUBSTITUTE replaces the nonbreaking space character with a regular space before TRIM removes extra spaces. This does not solve every possible encoding or punctuation difference, so compare the cleaned values if a match is still missing.

If one side contains numbers stored as text, standardize both columns to the same type. For IDs where leading zeros matter, keep both sides as text: converting “0042” to a number would change it to 42. For values that are genuinely numeric, you can use Excel’s conversion tools, such as the warning menu that appears for numbers stored as text, or build a cleaned helper column. Avoid changing formats alone and expecting the underlying value to change; number formatting affects appearance, not necessarily the stored data type.

Understand common error messages

#N/A: The lookup value was not found as an exact match, or the formula’s range does not include its row. Check spelling, spaces, data types, and the selected range.
#REF!: The requested result-column number is greater than the number of columns in the selected range, or a referenced column was deleted. For example, a column number of 3 cannot return a value from a two-column range.
#VALUE!: An argument may be invalid, including a result-column number that is less than 1. Check the formula’s arguments and cell references.
#NAME?: Excel may not recognize the function name or text in the formula. Check the spelling of VLOOKUP and make sure text values in formulas are enclosed in straight quotation marks where required.

A formula can also return a plausible but incorrect answer without displaying an error. A missing FALSE argument may make Excel use approximate matching; an incorrect column number may return the wrong field; and duplicate IDs may cause VLOOKUP to return the first occurrence. Verify a few results against the source rows before relying on a large copied formula.

Correct the range and result-column number

The column number is counted inside the selected range, not from the worksheet’s column letters. If the range is C:F, then C is column 1, D is 2, E is 3, and F is 4. A common mistake is to count from column A or to assume that the number identifies the worksheet column. If you see #REF!, check this count first.

The lookup column must also be the range’s leftmost column. If employee names are in B and employee IDs are in A, VLOOKUP cannot search B and return A using a range that starts in A. Rearrange the source columns, create a helper range with the lookup field first, or use a lookup function that can return data from a column on either side. Do not select a range that starts one column too far left or right: that changes both the search column and the meaning of the result-column number.

When a worksheet column is inserted or deleted, a hard-coded result-column number can become misleading or point to a different field. For a formula that needs to remain understandable as a table changes, consider XLOOKUP or an INDEX and MATCH construction. In Microsoft 365 for Windows, XLOOKUP is available; the Mac version of Microsoft 365 also includes it. Excel for the web supports it as well, although some interface details differ.

Check approximate matching and wildcards

If the last VLOOKUP argument is TRUE or omitted, Excel uses approximate matching. For this mode, the first column of the lookup range must be sorted in ascending order. It returns the closest value that is less than or equal to the lookup value, rather than requiring an identical value. That behavior can be useful for tax-rate bands, grading thresholds, or commission tiers, but only when the thresholds and sorting are intentional. A misplaced or unsorted threshold can produce a misleading result.

For text searches, FALSE means an exact match, but Excel also recognizes wildcard characters in the lookup value: an asterisk represents any number of characters, a question mark represents one character, and a tilde can escape a wildcard. Thus, a lookup value containing * may match more than a literal asterisk. If the symbol is part of an actual code, prefix it with a tilde in the lookup value. This detail matters for IDs and filenames that contain punctuation.

Handle missing results without hiding problems

If a missing match is an expected possibility, you can replace the displayed #N/A with a clear message. With codes in A2:A4, names in B2:B4, and the search value in D2, use: =IFNA(VLOOKUP(D2,$A$2:$B$4,2,FALSE),"Not found"). If D2 contains a code that is absent, the formula returns Not found; if it contains 205 in the example above, it returns Mira. IFNA handles #N/A specifically, so other problems such as #REF! remain visible and can still be fixed.

Avoid wrapping every lookup in IFERROR simply to make the sheet look clean. IFERROR can hide structural errors as well as missing matches, making a broken column number appear to be ordinary missing data. If you use it for a deliberate reason, investigate the formula first and choose a fallback that makes sense for readers. A blank result is not always better than a visible error.

When to choose XLOOKUP or another alternative

VLOOKUP remains useful for simple, stable tables and for compatibility with older workbooks. XLOOKUP is often clearer in current Excel because it takes separate lookup and return ranges, can return values from columns to the left or right, and uses exact matching by default. With codes in A2:A4, names in B2:B4, and the search value in D2, use: =XLOOKUP(D2,A2:A4,B2:B4,"Not found"). For D2 equal to 205, the result is Mira; for an absent code, it returns Not found. XLOOKUP is available in Microsoft 365, Excel 2021, and later supported editions, but not in some older Excel releases, so check the workbook’s audience before replacing a legacy formula.

INDEX and MATCH can also look in either direction and are useful in older Excel versions where XLOOKUP is unavailable. They require more syntax, so VLOOKUP may be easier to maintain when its left-to-right layout fits the data. For a one-off lookup, choose the simplest formula that works in the versions used by everyone who will open the workbook. If a result must be unique, separately check for duplicate lookup keys; changing the lookup function does not make duplicates disappear.

Frequently Asked Questions

Why does VLOOKUP return the wrong value? The formula may be using approximate matching because its final argument is TRUE or omitted, the result-column number may point to the wrong field, or the first matching key may be a duplicate. Use FALSE for an exact match, count columns from the range’s left edge, and check whether the lookup column contains duplicates.

Why does VLOOKUP show #N/A when the value is visibly there? The two values may differ in data type, contain hidden spaces or characters, or differ by a small spelling or punctuation change. Confirm that the visible value is in the range’s first column, then compare the underlying values and test for a match with COUNTIF.

How do I copy VLOOKUP down without changing the table range? Make the range absolute with dollar signs, such as $A$2:$B$100, while leaving the lookup cell relative if each row should use its own search value. You can also store the data in an Excel table so its range adjusts when rows are added.

Can VLOOKUP return a value from a column to the left? No. VLOOKUP searches the first column of its range and returns only from a column to its right. Rearrange or copy the data into a lookup-first range, or use XLOOKUP, which can return from either side.

Does VLOOKUP distinguish uppercase and lowercase? No. VLOOKUP’s text matching is not case-sensitive, so ABC and abc are treated as equivalent for a lookup. If capitalization must distinguish records, use a case-sensitive approach rather than relying on VLOOKUP alone.

Why did my result change after I added rows? A fixed range may not include new records, or a copied formula may have shifted its range. Extend the reference or use an Excel table that expands with added rows, then verify that the new records are included in the lookup column.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.