VLOOKUP Formula Not Working? Fix the Common Errors

If VLOOKUP is not working, first check that its lookup value is in the leftmost column of the selected range, that the column number is correct, and that the final argument is FALSE for an exact match. Most errors come from a mismatched value, an incorrect range, or an omitted match-type argument—not from a broken Excel installation.

How VLOOKUP works

VLOOKUP searches down the first column of a range and returns a value from another column in the same row. Its syntax is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The column number is counted from the left edge of the selected range, not from the worksheet. The optional final argument controls whether Excel looks for an exact or approximate match.

For example, with order IDs in A2:A4, item names in B2:B4, prices in C2:C4, and the ID to find in E2, use =VLOOKUP(E2,A2:C4,3,FALSE). If E2 contains 106 and row 4 contains 106, Desk lamp, and 19.95, the result is 19.95. Here, 3 means the third column of A2:C4, which is column C. VLOOKUP cannot return a value from a column to the left of the lookup column.

In Microsoft Excel for Microsoft 365 on Windows, specify FALSE when you want an exact match. If you omit the fourth argument, VLOOKUP uses approximate matching, which can return a plausible but incorrect result instead of an error.

Run these checks first

Before changing your data, inspect the formula from left to right. A small reference mistake can make a sound formula appear unreliable, especially when it has been copied down a long column.

Confirm that the value being searched for is in the first column of the table range.
Check that the range includes both the lookup column and the column containing the result.
Count the result column from the left edge of that range and check the column index.
Use FALSE for an exact match unless you specifically need approximate matching.
Check whether the lookup value and source values are the same underlying type, such as numbers rather than numbers stored as text.
Look for extra spaces, duplicate keys, and a range that shifted when the formula was copied.

To inspect a complicated formula in Excel for Windows, select its cell and choose Formulas > Evaluate Formula. This steps through the calculation and can reveal which lookup value or range Excel is actually using. Formulas > Trace Precedents can also show which cells feed the selected formula.

Fixing #N/A when a value appears to be there

#N/A usually means an exact-match lookup did not find an identical value in the first column of the selected range. A value can look identical on screen but differ underneath: one may contain a trailing space, one may be text while the other is numeric, or the two cells may contain different characters.

Check the source and lookup cells directly. Click each cell and inspect the formula bar, or temporarily compare their lengths if they contain text. Pay particular attention to pasted data from websites, exported reports, and systems that add spaces or nonprinting characters. The worksheet display may hide these differences.

If extra ordinary spaces or certain nonprinting characters are the issue, a helper column can clean the source keys. With the original text key in A2, enter =TRIM(CLEAN(A2)) in a helper cell on the same row. TRIM removes excess ordinary spaces, and CLEAN removes many nonprinting ASCII control characters. Neither function removes every kind of unusual Unicode space, so inspect persistent mismatches rather than assuming the helper fixes all imported text.

Check number-versus-text mismatches carefully. A cell containing the text “104” is not necessarily equivalent to the numeric value 104, even though both may display as 104. Applying a number format does not reliably convert existing text into numbers. If the values are identifiers that may have leading zeros, such as 00104, keep both sides as text; converting them to numbers would lose those zeros. For ordinary numeric data, convert values consistently in a separate column or use Excel’s data-conversion tools, then check the results before replacing source data.

Also verify that the key really is in the first column of the table array. If the IDs are in column B but the formula range starts in column A, VLOOKUP searches A, not B. Adjust the range so it starts with the ID column, or use a lookup function that permits a separate lookup range.

Prevent wrong results from approximate matching

A particularly confusing failure is a result that looks reasonable but belongs to the wrong row. One frequent cause is a missing fourth argument. When it is omitted or set to TRUE, VLOOKUP performs approximate matching. That behavior is useful for ranges such as tax bands or grade thresholds, but it is unsafe for ordinary IDs and names.

Approximate matching expects the first column to be sorted in ascending order. Excel returns the matching value or, when it does not find an exact value, the result associated with the largest first-column value that is less than the lookup value. If the column is unsorted, the result may be unreliable. For example, with minimum scores in A2:A5 in ascending order (0, 60, 70, 80), grades in B2:B5, and a score of 74 in E2, =VLOOKUP(E2,A2:B5,2,TRUE) returns the grade in the row for 70, which is C if that row is labeled C.

For product codes, employee IDs, invoice numbers, and most name searches, use exact matching. Exact matching does not require the lookup column to be sorted. It also returns the first matching row if the key occurs more than once, so duplicate keys can explain why Excel returns an unexpected but technically valid result.

Check the range and column number

The table array must start with the lookup column and extend far enough to include the answer column. The column index is a count within that range: in A2:C100, A is 1, B is 2, and C is 3. If you select B2:D100 instead, the same worksheet column C has index 2 within the selected range.

A column index larger than the number of columns in the range commonly produces #REF!. A column index below 1 can produce #VALUE!. Correct the range or the index rather than hiding the error. If you insert or rearrange columns, revisit formulas that depend on a numeric index: VLOOKUP does not identify the result column by its heading.

When copying a formula down, use absolute references for a fixed lookup table. With order IDs in E2:E20 and the source table in A2:C100, enter this in F2: =VLOOKUP(E2,$A$2:$C$100,3,FALSE). The lookup value changes to E3, E4, and so on when copied down, while the dollar signs keep the source range fixed. In Excel for Windows, F4 cycles through reference styles while editing a cell reference; on a Mac, the equivalent commonly uses Fn+F4, depending on keyboard settings.

Resolve text, spaces, and formatting mismatches

Formatting changes how a value looks; it does not always change what the cell contains. A date is stored as a serial number and displayed according to its number format. A number formatted with leading zeros may display as 00104 while its stored value is 104. If the other table stores the ID as text, exact matching can fail. Decide whether a key is meant to be numeric or textual, then make both lookup and source columns consistent.

Text matching in VLOOKUP is not case-sensitive, so ABC and abc are treated as the same text for this purpose. However, punctuation, spaces, and visually similar characters can still differ. For example, a standard hyphen and a longer dash are distinct characters. If a lookup still fails after trimming, compare the actual source values and consider rebuilding the key from clean source fields.

When a formula displays as text instead of calculating, check whether the cell is formatted as Text or whether an apostrophe appears before the equals sign. Change the cell to General using Home > Number Format, then re-enter the formula. Changing the format alone may not recalculate a formula already stored as text. If results seem stale, check Formulas > Calculation Options > Automatic; a workbook set to manual calculation may need recalculation.

Show a useful message without hiding every problem

You can replace the expected “not found” result with a clear message while keeping other formula errors visible. With the order table in A2:C4 and the lookup ID in E2, use =IFNA(VLOOKUP(E2,$A$2:$C$4,3,FALSE),"Not found"). If E2 contains 106 and the matching price is 19.95, the formula returns 19.95; if there is no matching ID, it returns Not found.

IFNA catches only #N/A, which is generally preferable for a missing lookup. IFERROR catches a wider range of errors and can conceal a faulty range or column number by replacing that error with a friendly message. During troubleshooting, fix the cause first instead of masking it.

When to use XLOOKUP instead

Excel for Microsoft 365 includes XLOOKUP, which can search one range and return a result from a separate range. Unlike VLOOKUP, it can return values to the left or right of the lookup column and does not require a numeric column index. With order IDs in A2:A4, prices in C2:C4, and the lookup ID in E2, use =XLOOKUP(E2,A2:A4,C2:C4,"Not found"). For ID 106, this returns 19.95; for an absent ID, it returns Not found.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but it is not supported in some older Excel versions. If a workbook must be opened in an older version, VLOOKUP or INDEX and MATCH may be a more compatible choice. The main reason to keep a working VLOOKUP is often compatibility; the main reason to replace it is a need for a more flexible lookup direction or clearer separate lookup and return ranges.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is in the table?
It may not be an exact underlying match. Check whether the value is stored as text on one side and a number on the other, whether hidden spaces or unusual characters are present, and whether the lookup column is the first column in the selected range.

Why is VLOOKUP returning the wrong row?
Check whether the fourth argument was omitted or set to TRUE, which requests approximate matching. Use FALSE for an exact lookup. If exact matching is already selected, check for duplicate keys: VLOOKUP returns the first matching row.

Does VLOOKUP need the lookup column sorted?
No, not when you use FALSE for exact matching. Approximate matching with TRUE requires the first column to be sorted in ascending order for reliable results.

Can VLOOKUP search to the left?
No. VLOOKUP searches the leftmost column of its table range and returns values from columns to its right. Use XLOOKUP in a supported Excel version, or use INDEX and MATCH, if the result is to the left.

Why does my formula show instead of a result?
Check whether the cell is formatted as Text, whether the formula starts with an apostrophe, and whether calculation is set to Automatic. Change the cell to General and re-enter the formula if Excel stored it as text.

Is VLOOKUP case-sensitive?
No. VLOOKUP does not distinguish uppercase from lowercase in text matches. It can still distinguish other differences, including punctuation, extra spaces, and different characters that look similar.

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.