Why Is My VLOOKUP Not Working? Excel Fixes

VLOOKUP usually fails because the lookup value does not exactly match the first column of its table, the formula uses the wrong column number, or Excel is doing an approximate match instead of an exact one. Start by adding FALSE as the fourth argument, then check the lookup column, range, and data types.

What VLOOKUP expects

VLOOKUP searches vertically down the first column of a table 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 lookup value can be a cell reference or a value; the table array is the range to search; the column index counts the return column from the left edge of that range; and the optional final argument controls whether the match is exact or approximate.

For example, with product codes in A2:A100, product names in B2:B100, prices in C2:C100, and the code to find in E2, the formula below returns the price from column C. Because A is the first column of the selected table and C is its third column, the index is 3.

With product codes in A2:A100, names in B2:B100, prices in C2:C100, and the code to find in E2, use =VLOOKUP(E2,A2:C100,3,FALSE). If E2 contains a code present in column A, the result is the price from the matching row in column C.

VLOOKUP has an important direction limit: it can only search the first column of the selected table and return a value to its right. It cannot look in column C and return a value from column A using the same table arrangement. That limitation is a frequent source of formulas that seem logically correct but cannot produce the requested result.

Make sure the match type is exact

If you omit the fourth argument, Excel treats it as TRUE and uses approximate matching. Approximate matching is useful for sorted bands, such as tax brackets or grading thresholds, but it is not the right choice for most searches by name, ID, or product code. It expects the first column of the table to be sorted in ascending order. With unsorted data, it may return an unexpected row rather than a clear error.

For ordinary lookups, specify FALSE. FALSE tells Excel to find an exact match; if it cannot find one, VLOOKUP normally returns #N/A. TRUE explicitly requests approximate matching, and leaving the argument out has the same effect as TRUE. Do not assume Excel will choose exact matching just because the values look like unique IDs.

With IDs in A2:A20, corresponding departments in B2:B20, and the ID being searched in D2, use =VLOOKUP(D2,A2:B20,2,FALSE). If D2 exactly matches an ID in column A, the formula returns the department from column B on that row.

Use the error message to narrow the cause

The error itself often points toward the next check. #N/A generally means Excel did not find a match under the selected match rules. That may mean the value is genuinely absent, but it can also mean the two values only look alike: one may be stored as text while the other is numeric, or one may contain an extra space.

#REF! commonly appears when the column index asks for a column outside the table range, or when a referenced range has been deleted.
#VALUE! can indicate an invalid column index, such as an index less than 1. Check that the third argument is a positive number.
#NAME? can result from misspelling VLOOKUP or entering text in a formula without quotation marks.
A plausible but wrong result often points to approximate matching, an incorrectly selected table, or duplicate lookup values.

For instance, if the table range is A2:B20, its second column is the last available return column. An index of 3 cannot return a value from that range. Expand the table to include the intended return column or correct the index. If a formula returns #N/A, first verify the exact-match argument and inspect both the lookup cell and the corresponding table values before wrapping the formula in error-handling logic.

Check the table range and column number

The column index is counted from the first column in table_array, not from column A on the worksheet. If the selected range is B2:D100, then B is column 1, C is column 2, and D is column 3. A formula using index 3 returns from D, even though D is the fourth worksheet column. This distinction matters when the table begins somewhere other than column A.

Also confirm that table_array includes both the lookup column and the column containing the answer. If the lookup is in A and the result is in D, the selected range must span A through D, and D is index 4. A carefully chosen range is better than selecting entire columns by habit, especially in large workbooks where the formula is copied many times.

When copying a formula down, a relative table range can shift. For example, a range beginning at A2 may become A3 in the next row, leaving out the first record. Lock the table range with dollar signs while allowing the lookup cell to change. With lookup values in E2:E100 and the table in A2:C100, use =VLOOKUP(E2,$A$2:$C$100,3,FALSE) in the first result row and fill it down. The range stays fixed while E2 changes to E3, E4, and so on.

Look for text-versus-number mismatches

Excel can store a number as a number or as text. A cell formatted to display zero decimal places is still numeric, but a value imported with a leading apostrophe or from a text file may be stored as text. A text value such as “00427” is not necessarily equivalent to the number 427: the leading zeros may be meaningful parts of an ID. Changing the cell’s number format alone does not convert text into a number.

Compare the source and lookup values and decide what the data represents before converting anything. For a numeric quantity, you might convert text numbers to numbers using Data > Text to Columns on Windows, following the prompts and choosing Finish. For identifiers such as account codes, preserve leading zeros and make both sides consistent as text instead. Do not convert a code to a number if doing so changes its intended identity.

You can also make a helper column to convert a text number when that conversion is appropriate. With a numeric-looking text value in A2, enter =VALUE(A2) in a helper cell. If A2 contains the text 427, the result is the number 427. Use converted values consistently in the lookup column and lookup cell; converting only one side may leave the mismatch in place.

Remove hidden spaces and other differences

Two entries can appear identical on screen but differ because of leading spaces, trailing spaces, repeated spaces, or characters copied from a website or exported file. A regular space and a nonbreaking space are different characters. Imported data can also contain nonprinting characters. Check the original source and, when appropriate, clean the data in helper columns before relying on a lookup.

TRIM can remove leading and trailing ordinary spaces and reduce repeated ordinary spaces between words to single spaces. CLEAN removes certain nonprinting characters. Neither function is a universal fix for every Unicode or nonbreaking-space issue, so test the cleaned values against the actual source. For example, with names in A2:A100, a helper column can use =TRIM(CLEAN(A2)). If A2 contains ordinary extra spaces and characters CLEAN recognizes, the helper result is the cleaned text; fill the helper formula down and use the cleaned column consistently.

VLOOKUP’s exact text matching also treats asterisks and question marks as wildcard characters: an asterisk can represent any sequence of characters, and a question mark can represent one character. If the lookup text is intended to contain a literal wildcard, escape it with a tilde in the search value. This is unusual for everyday names but can matter when codes or labels include those characters.

Account for duplicates and approximate matches

When multiple rows have the same lookup value, exact-match VLOOKUP returns the first matching row it encounters, scanning from the top of the selected table. It does not report that the value is duplicated or choose the newest, largest, or most relevant match automatically. If duplicates are possible, sort or filter the source data to understand which record appears first, or use a method designed to return all matching rows.

Approximate matching has a different purpose. In a sorted threshold table, it returns the row whose first-column value is the largest value less than or equal to the lookup value. For example, a table of minimum score thresholds can map a score to a band. The threshold column must be sorted ascending, and the meaning of the boundaries must be planned carefully. If the lookup value is smaller than the first threshold, there may be no suitable approximate match.

For ordinary employee numbers, invoice IDs, or product codes, approximate matching is usually a mistake. A wrong approximate result can look convincing because it is a valid value from the table, making it more dangerous than an obvious error. Setting the final argument to FALSE helps prevent this class of silent mismatch.

Handle missing results without hiding mistakes

Sometimes a missing match is expected and you want a readable message rather than #N/A. IFNA can catch that particular error while leaving other formula errors visible. This is often safer during troubleshooting than IFERROR, which can conceal issues such as a broken reference or an invalid column index along with a genuinely missing lookup.

With lookup values in E2, the lookup table in A2:C100, and the desired result in its third column, use =IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found"). If E2 has no exact match, the result is Not found; if it matches, the formula returns the corresponding third-column value. Once the workbook is working, decide whether a message is appropriate for your process or whether an explicit error is more useful for spotting incomplete data.

When XLOOKUP may be a better fit

In Microsoft 365 for Windows, XLOOKUP is available and can search one range while returning a value from another, including a column to the left. It also uses exact matching by default, so you do not have to count a return-column index. XLOOKUP is available in Microsoft 365 and Excel 2021; older Excel versions may not support it, and a workbook using it may not calculate correctly for someone using an older release.

With product codes in A2:A100, prices in C2:C100, and a code in E2, use =XLOOKUP(E2,A2:A100,C2:C100,"Not found"). If E2 matches a code, the result is its price; if not, the formula returns Not found. XLOOKUP is not mandatory: VLOOKUP remains useful for compatibility and familiar tables. In Excel for Mac, the function’s availability depends on the Excel version, just as it does on Windows; Excel for the web also supports modern lookup functions, though the interface differs.

A practical troubleshooting sequence

Work through these checks in order rather than changing several things at once. That makes it easier to identify the actual cause and avoid masking an incorrect result.

Confirm that the formula is spelled VLOOKUP and its arguments are in the right order.
Set the fourth argument to FALSE for an ordinary exact lookup.
Check that the lookup value is in the first column of the selected table range.
Count the return column from the left edge of that range and verify that the index is within its width.
Compare the underlying values for text-versus-number differences, spaces, and other imported characters.
Check whether the lookup value exists and whether duplicate values affect which row is returned.
If the formula is copied, lock the table range and verify that the lookup reference changes as intended.

Use a small known example to confirm the formula: choose a lookup value whose row and expected return value you can identify directly in the source table. If that works but other rows fail, the problem is likely in particular data values rather than the formula structure. If no rows work, revisit the table’s first column, range boundaries, and match argument.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is there?
It may only appear to be the same value. Check whether one entry is text and the other is numeric, whether spaces or imported characters are present, and whether the lookup column is the first column in the selected range. For ordinary searches, use FALSE for an exact match.

Why is VLOOKUP returning the wrong value?
Common causes include leaving the match argument out, using TRUE on an unsorted table, selecting the wrong return column, or having duplicates where the first match is not the record you expected. Check the formula’s table range and count the column index from that range’s left edge.

Can VLOOKUP look to the left?
No. VLOOKUP searches the first column of its table range and returns a value from a column to its right. Rearrange the table, use INDEX and MATCH, or use XLOOKUP in a supported version if the return value is to the left.

Why does VLOOKUP show #REF!?
The return-column index may exceed the number of columns in the selected table, or a referenced cell or range may have been deleted. Expand or correct the table range, then check that the index points to a column inside it.

Does formatting a number as text fix a VLOOKUP mismatch?
Not necessarily. Number formatting changes how a value is displayed, not always how it is stored. Convert values deliberately and consistently, taking care not to remove meaningful leading zeros from identifiers.

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.