VLOOKUP Not Working With Text? How to Fix It

When VLOOKUP will not find text, the most common causes are an approximate-match setting, extra or nonprinting spaces, or a mismatch between text and numbers stored as text. In Excel for Microsoft 365 on Windows, start by using an exact-match formula such as =VLOOKUP(E2,$A$2:$C$100,3,FALSE) and checking that the lookup value in E2 matches a value in the first column of the selected range.

Why VLOOKUP can fail on text

VLOOKUP compares the value in its lookup cell with values in the first column of its table array. It does not judge whether two entries look alike on screen; it compares the underlying cell contents. That distinction matters when a code, account number, or name has been copied from a website, exported from another system, or entered with a different data type.

For example, the entries North and North look almost identical, but the second contains a trailing space. Likewise, the number 1042 and the text value 1042 can display identically while remaining different types. A cell’s number format changes how a value appears; it does not necessarily change the stored value or make text and numbers equivalent.

VLOOKUP also has structural requirements. The lookup values must be in the leftmost column of the selected range, and the column number must point to the column containing the answer. A text problem may therefore be a range or column-index problem rather than a problem with the text itself.

Check exact-match mode first

The fourth argument in VLOOKUP controls the match mode. If it is omitted or set to TRUE, Excel uses approximate matching. Approximate matching is intended for ordered ranges such as tax bands or grading thresholds; the first column generally needs to be sorted in ascending order. For names, labels, product IDs, and most lookup lists, use FALSE for an exact match.

Suppose A2:A100 contains product IDs, B contains descriptions, and C contains availability. E2 contains the ID to find. With that layout, this formula returns the availability from column C for an exact match; if E2 is A-104 and the matching row has Open in column C, the result is Open:

=VLOOKUP(E2,$A$2:$C$100,3,FALSE)

The dollar signs keep the table range fixed if you copy the formula down. They do not alter matching. If the formula returns the wrong row or an unexpected result, verify that the last argument is FALSE and that the result column is actually the third column of A:C.

Diagnose the error or wrong result

The message in the result cell can narrow down the cause. #N/A usually means Excel did not find a match under the selected match rules. #REF! commonly means the column index asks for a column outside the table array, while #VALUE! can indicate an invalid argument, including a column index below 1. A result that looks plausible but belongs to another row can point to approximate matching or an incorrect table range.

Check these items in order before rebuilding the formula:

Confirm the lookup value is in the first column of the table array.
Use FALSE as the fourth argument for an exact lookup.
Check that the table array includes both the lookup column and the return column.
Count columns from the left edge of the array to verify the column index.
Compare the source and lookup values for spaces, punctuation, and data-type differences.
Confirm that the formula refers to the intended cells, especially after copying it to another row.

For a quick visual check, select a suspicious cell and inspect the formula bar, then click the cell and look at the formula bar’s beginning and end for accidental spaces. You can also test whether two cells are exactly equal. With the lookup value in E2 and a candidate value in A2, the formula =E2=A2 returns TRUE if Excel considers them equal and FALSE otherwise. This test does not explain every mismatch, but it helps distinguish a data issue from a VLOOKUP-range issue.

Text numbers and numeric values

Imported identifiers often mix numbers and text. One file may store 1042 as a number, while another stores the characters “1042” as text. If the lookup value and the first-column value have different types, an exact lookup can fail even though both cells display 1042. The small warning marker sometimes shown in Excel can indicate a number stored as text, but it may not appear in every workbook.

Choose a consistent type based on what the values mean. For quantities that should be used in arithmetic, numbers are usually appropriate. For identifiers such as postal codes, employee IDs, and product codes, text is often safer: leading zeros can be significant, and identifiers are not necessarily quantities.

With the lookup value stored as text in E2, numeric IDs in A2:A20, and return values in B2:B20, this formula converts E2 to a number before searching. If E2 contains the text 1042 and A7 contains numeric 1042, it returns the value in B7:

=VLOOKUP(VALUE(E2),$A$2:$B$20,2,FALSE)

Use that approach only when the lookup text is a valid number and converting it will not discard meaningful formatting. For example, converting the text code 00142 to a number produces 142, which is not the same identifier if the leading zeros matter. If the lookup column contains text IDs and E2 is a numeric value, a helper column that consistently converts the source IDs to numbers may be more reliable than changing the lookup value alone.

Remove extra and hidden characters

Ordinary leading and trailing spaces are frequent causes of failed text lookups. Excel’s TRIM function removes leading and trailing regular spaces and reduces repeated spaces between words to one. CLEAN removes many nonprinting control characters, which can arrive with copied or imported data. However, TRIM does not remove every character that looks like a space. A nonbreaking space, often found in web data, is a common exception.

If A2 contains a source value, B2:B100 contains related return data, and you want a cleaned lookup key in C2, this formula replaces nonbreaking spaces with regular spaces, removes control characters, and trims the result. For example, a value with a leading space and a nonbreaking space at the end is cleaned for use as a key:

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

Apply the same cleaning process to the lookup value and the values in the source lookup column. Cleaning only E2 will not help if the unwanted character is in the table, and cleaning only the table will not help if the lookup cell is dirty. A practical setup is to make a helper column beside the source data, fill the cleaning formula down, and point VLOOKUP at that helper column as the first column of its table array. Keep the original data intact until you have confirmed the cleaned keys do not accidentally merge distinct records.

Check the lookup range and column index

VLOOKUP searches only the first column of its table array. If IDs are in column B but the selected array begins at A, Excel searches the wrong values. The return column must also be within the array. The column index counts from the array’s first column, not from worksheet column A: in a range starting at B, column B is index 1 and column C is index 2.

A common misconception is that VLOOKUP can search a column to the right and return a value to its left. It cannot do that directly. If the lookup key is in column C and the desired answer is in column A, rearrange the data or use another lookup method. Also make sure the range is anchored when copying a formula; an unanchored range can shift from row to row and eventually exclude the matching record.

Text matching, capitalization, and wildcards

VLOOKUP’s exact-match mode is not case-sensitive. Searching for abc can match ABC, so changing capitalization alone will not usually explain an exact-match failure. If capitalization is meaningful in your data, standard VLOOKUP is not a case-sensitive lookup tool; use a method designed to compare case, or add a separate key that distinguishes the records.

There is another subtlety: in exact-match mode, a lookup value containing an asterisk (*) or question mark (?) can act as a wildcard. An asterisk can represent a sequence of characters, and a question mark can represent one character. A tilde (~) is used to treat a wildcard character literally. This matters for product descriptions and codes that genuinely contain those characters; the formula may match a broader value than expected even with FALSE specified.

For ordinary text with no wildcard characters, exact-match mode should find the whole value, not a partial word. If a search for a short word appears to return a longer description, inspect the lookup value for wildcard symbols and confirm the formula is not using approximate matching.

When XLOOKUP is a better option

In Microsoft 365, XLOOKUP can make a lookup formula easier to read because it accepts a lookup range and a return range separately. It also uses exact matching by default and can display a chosen message when there is no match. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it is not available in some older perpetual Excel editions.

With IDs in A2:A100, results in C2:C100, and the search ID in E2, this formula returns the corresponding value from column C or Not found if there is no match. If E2 is A-104 and the matching C cell contains Open, the result is Open:

=XLOOKUP(E2,A2:A100,C2:C100,"Not found",0)

XLOOKUP can return a value from a column to the left or right of the lookup range, avoiding VLOOKUP’s leftmost-column restriction. It does not automatically fix inconsistent types, hidden spaces, or dirty imported values, though. The source keys still need to be consistent. If a workbook must be opened in an older Excel version that lacks XLOOKUP, VLOOKUP or INDEX and MATCH may be more compatible choices.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the text looks identical?
Excel may be comparing different underlying values, such as text versus a number, or one value may contain a leading, trailing, nonbreaking, or nonprinting character. Check the match mode and clean or standardize both the lookup value and the source keys.

Does VLOOKUP distinguish uppercase and lowercase?
No. VLOOKUP’s text matching is not case-sensitive, including when its fourth argument is FALSE. If capitalization must distinguish two records, use a case-sensitive lookup method or create a separate key that encodes the distinction.

Does changing a cell to Text fix a failed lookup?
Not by itself. Changing a number format affects display and may not convert a value already stored in another type. Convert the actual lookup and source values consistently, while preserving leading zeros when they are part of an identifier.

Can VLOOKUP search to the left?
No. The lookup column must be the first column in the selected table array, and the return column must be to its right. Use XLOOKUP in a supported Excel version or restructure the range if the answer is to the left.

Why does VLOOKUP return the wrong text result?
Check whether the formula uses approximate matching, whether the lookup column is sorted when approximate matching is intentional, and whether the table range and column index are correct. With text labels, an exact match using FALSE is usually the appropriate setting.

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.