Excel VLOOKUP Not Working With Text? Fix the Match

If VLOOKUP is not finding text in Excel, first use an exact match and check that the lookup value and the first column of the table contain the same kind of data. A value that looks like text may actually be a number, or the two entries may differ because of spaces or invisible characters.

How VLOOKUP matches text

VLOOKUP searches for a value in the first column of a table and returns a value from another column in the same row. For a text lookup, its most reliable default is an exact match: set the final argument to FALSE. If you omit that argument, VLOOKUP uses approximate matching, which can return an unexpected result or depend on the first column being sorted.

With the lookup value in E2 and a table in A2:C100, where the desired result is in the third column, use:

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

The dollar signs keep the table range fixed if you copy the formula to other rows. The 3 tells Excel to return the value from the third column of that range, and FALSE asks for an exact match. Exact means the stored values must match; it does not mean Excel checks capitalization. VLOOKUP is not case-sensitive, so “North” and “NORTH” are treated as the same text.

VLOOKUP also has a structural requirement: it searches only the leftmost column of the selected table. If the lookup field is in column B, a table range starting in A will not make VLOOKUP search B as its key column. Select a range that begins with the lookup column, or use another lookup method.

Check the match type and the lookup range

When a text lookup returns #N/A, verify the formula’s arguments before changing the data. The lookup value should refer to the cell you intend to search for, the table array should include the key column and return column, and the column index should count from the left edge of that array—not from column A on the worksheet.

Use FALSE as the fourth argument when you need an exact match. TRUE, or an omitted fourth argument, requests approximate matching.
Make sure the lookup key is in the first column of the table array.
Check that the return column is inside the array and that the column index is not larger than the array’s width.
Use absolute references for a table range that should not move when the formula is filled down.

Approximate matching is useful for certain sorted ranges, such as grade thresholds, but it is usually wrong for names, codes, product IDs, and other text keys. If an approximate-match formula searches an unsorted list, its result may be unreliable. For ordinary text lookups, make FALSE explicit rather than relying on a default.

Text that looks like a number

Excel distinguishes a number such as 4821 from the text string “4821,” even though both may look identical in a cell. This commonly happens when data is imported from a CSV, copied from a website, or entered with a leading apostrophe. A lookup value stored as a number may not match a table key stored as text, and the reverse can also be true.

To inspect a value, use Excel’s ISNUMBER and ISTEXT functions. With the suspect value in E2, these checks return TRUE or FALSE according to its stored type:

=ISNUMBER(E2) and =ISTEXT(E2)

Changing a cell’s number format to Text after the value has been entered does not necessarily convert the stored value. Likewise, applying General or Number formatting to text does not guarantee that Excel will turn it into a number. Choose a consistent type for the lookup column and the lookup value, then convert one side if appropriate.

If E2 contains a numeric-looking text value and the table’s keys are numbers, the following conversion can be used. With the text number in E2, this formula returns the number 4821 for an input of “4821”:

=VALUE(E2)

Conversion is suitable only when the key represents a number. It is usually a mistake for identifiers such as postal codes, account codes, or product numbers, where leading zeros may be meaningful. For example, converting the text ID “0047” to a number loses its leading zeros. In that case, keep both sides as text and ensure the text strings have the same characters.

For numeric values that should be represented as text, a helper column can convert a number into a consistent text form. With a number in E2 and a whole-number ID format, this returns the text “4821”:

=TEXT(E2,"0")

Use a format pattern that reflects the actual identifier rules. For a fixed-width code, a pattern such as “00000” can display a number with leading zeros, but converting codes is safest only when the intended width is known and consistent.

Remove extra spaces and hidden characters

Text may appear identical while containing a leading space, a trailing space, repeated spaces, or a nonbreaking space copied from a web page. These characters can prevent an exact match. Excel’s TRIM function removes ordinary leading and trailing spaces and reduces repeated ordinary spaces between words to one. CLEAN removes many nonprinting control characters, but it does not remove every character that can arrive in imported data.

A nonbreaking space, often found in copied web content, is a common exception. With the suspect text in A2, this helper formula replaces nonbreaking spaces with ordinary spaces, removes common control characters, and trims ordinary spaces:

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

Apply the same cleanup to the lookup keys and the value you search for. Cleaning only E2 will not help if the unclean text remains in the table’s first column. A practical method is to create helper columns beside both lists, fill the cleanup formula down, and point VLOOKUP at the cleaned key column. Once you confirm the results, you can keep the helper columns or paste cleaned values over a copy of the original data.

Do not delete spaces blindly if spaces are meaningful within the values. TRIM preserves a single ordinary space between words, which is generally suitable for names and descriptions, but it changes repeated spaces. For codes, first inspect a few examples and decide what the correct standard should be.

Leading zeros, punctuation, and case

Identifiers deserve special care because their appearance can carry meaning that arithmetic does not. If a source file contains the text code “00082,” changing it to a number produces 82 and discards information. A custom number format can make a numeric 82 display as 00082, but the displayed zeros are not part of the underlying number. A text key “00082” and a numeric key 82 are still different stored values for lookup purposes.

Choose a consistent representation for both lookup columns. For codes with fixed-width zeros, store both sides as text in that same format. Also compare punctuation, hyphens, apostrophes, and similar-looking characters: a standard hyphen and a longer dash are different characters, even if they look close at normal zoom.

Capitalization alone is not normally the cause of a VLOOKUP miss because VLOOKUP ignores case. If capitalization must distinguish records—for example, if “ab12” and “AB12” are separate keys—VLOOKUP is not suitable by itself. Excel’s EXACT function can test case-sensitive equality, but a case-sensitive lookup generally needs a different formula design or a unique helper key.

Find the source of a #N/A result

#N/A usually means that Excel did not find a matching key under the formula’s rules. Instead of immediately wrapping the formula in an error handler, compare the two values and inspect the data. An error handler can make a report look tidy, but it does not correct mismatched types, extra characters, or a faulty table range.

A useful troubleshooting sequence is:

Confirm that the formula points to the intended lookup cell and table.
Set the fourth argument to FALSE and verify that the first table column contains the keys.
Check whether the lookup value and table key are both text or both numbers.
Inspect for leading or trailing spaces, nonbreaking spaces, and inconsistent punctuation.
Test with a value copied directly from the key column. If that works, compare the original lookup input with the copied value.

To identify whether a cleaned value finds a match, COUNTIF can provide a quick check. With the cleaned lookup value in E2 and keys in A2:A100, the result is the number of exact occurrences:

=COUNTIF($A$2:$A$100,E2)

A result of 0 indicates no matching value in that range under COUNTIF’s comparison rules; a result greater than 1 also warns that the key is duplicated, so VLOOKUP will return the first matching row. COUNTIF is not case-sensitive, like VLOOKUP.

If the formula returns the wrong value rather than #N/A, focus on the column index, duplicate keys, and approximate-match setting. VLOOKUP returns the first matching row it encounters. It cannot tell whether a duplicate is accidental or which duplicate you intended.

Use helper columns instead of hiding every problem

For recurring reports, a normalized key column is often easier to audit than a long formula that tries to fix every possible variation at lookup time. Add a helper column that applies an agreed cleanup or conversion rule to each key, then use that column consistently in the lookup table. Keep the original source field as well so that a changed key can be traced back to its imported value.

For example, if names may contain pasted nonbreaking spaces, create a cleaned version of each name and use those cleaned names as lookup keys. If codes must be text, convert and standardize them during the import or in a helper column, without stripping meaningful zeros. This approach also makes it easier to spot duplicates created by cleanup—for instance, when two entries differ only by extra spaces.

Power Query can be useful when the same external data needs repeatable cleanup on each refresh. In Microsoft 365 for Windows, open the data through Data > Get Data, make transformations in the Power Query Editor, and load the result back to Excel. Menu choices vary depending on the data source and workbook. For a one-off small list, a helper column is often simpler.

When to use XLOOKUP instead

In Microsoft 365, XLOOKUP is a more flexible alternative for many exact-match tasks. It can search one range and return from another range, so the return column does not have to be to the right of the lookup column. Its default match mode is exact, although stating the mode explicitly makes the intention clear.

With the lookup value in E2, keys in A2:A100, and return values in C2:C100, this formula returns “Not found” if there is no exact match:

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

XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it is not available in some older Excel releases. It does not automatically resolve text-versus-number mismatches or hidden spaces, so the same data checks still apply. VLOOKUP remains useful when a workbook must support older versions, or when an existing formula is already structured around a table array.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the text looks identical?
Usually the stored values differ: one may be text and the other a number, or one may contain a space, nonbreaking space, or different punctuation. Check the match type and compare cleaned versions of both keys.

Does VLOOKUP distinguish uppercase and lowercase?
No. VLOOKUP is case-insensitive, so capitalization by itself should not cause a missing match. Use a case-sensitive method if uppercase and lowercase must represent different keys.

Does formatting a cell as Text fix a VLOOKUP mismatch?
Not necessarily. Formatting changes how a value is displayed or treated for later entry; it does not reliably convert existing contents. Convert the stored values deliberately and make both lookup sides consistent.

Can VLOOKUP search for a value in the second column of my selected range?
No. VLOOKUP searches the leftmost column of its table array. Change the array so the key column is first, or use XLOOKUP in a supported Excel version to search and return from separate ranges.

Why does VLOOKUP return the wrong row?
Check for an omitted or TRUE fourth argument, which requests approximate matching, and confirm the column index. Also look for duplicate keys: VLOOKUP returns the first matching row in the search range.

Can I make VLOOKUP display a message instead of #N/A?
Yes. IFERROR can replace an error with a message, but it may also hide problems other than a missing key. Diagnose and correct the lookup first, then use an error message if it is appropriate for the workbook.

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.