Sometimes an Excel lookup fails even though the product code looks exactly right.
You might search for P-101, see P-101 in the source list, and still get a missing-match result. It’s tempting to assume there’s something wrong with XLOOKUP or VLOOKUP.
But the problem may be in the data itself.
A cell can contain extra spaces, invisible characters, different punctuation, or a number stored as text. These differences aren’t always obvious when you’re looking at a worksheet.
Before changing your lookup formula, it’s worth checking what Excel is actually comparing.
Create a Few Problem Values to Test
Open a blank Excel worksheet and enter these examples.
| Cell | What to enter | What it represents |
|---|---|---|
| A2 | =" P-101 " |
Product ID with ordinary spaces |
| A3 | =UNICHAR(160)&"P-101" |
Product ID with a non-breaking space |
| A4 | '00123 |
Identifier stored as text |
| A5 | ="P"&UNICHAR(8211)&"101" |
Product ID containing an en dash |
| A6 | '03/04/2026 |
Date stored as text |
The apostrophe before the values in A4 and A6 tells Excel to store those entries as text. It normally won’t appear in the displayed cell value.
These examples give us a few controlled problems to investigate without modifying a real product database.
1. Check for Extra Spaces With LEN and TRIM
Let’s start with cell A2.
It contains P-101 with a space before and after the product code.
You may not notice those spaces when looking at the cell, but Excel includes them in the stored text.
Enter this formula in another cell:
=LEN(A2)
The result is:
7 characters
Now try:
=LEN(TRIM(A2))
The result becomes:
5 characters
The difference tells us that ordinary spaces were present.
TRIM removes leading and trailing normal spaces and reduces repeated normal spaces between words to a single space.
For a product code that should contain no surrounding spaces, you can use:
=TRIM(A2)
The result is P-101.
What if TRIM Doesn’t Remove the Space?
Now look at A3.
This cell has a non-breaking space before P-101, created using UNICHAR(160).
If you apply TRIM directly, that particular character remains because Excel’s TRIM function is designed around ordinary spaces.
For this known character, try:
=TRIM(SUBSTITUTE(A3,UNICHAR(160)," "))
SUBSTITUTE changes the non-breaking space into a regular space. TRIM then removes the surrounding space.
This is useful when data has been copied from websites, documents, or other systems.
However, it isn’t a universal solution for every Unicode whitespace character. Identify the unwanted character before deciding how to replace it.
2. Check Whether a Number Is Stored as Text
Cell A4 contains:
00123
It looks like a number, but in this example it’s deliberately stored as text.
To check the value’s type, enter:
=ISNUMBER(A4)
The result is:
FALSE
That isn’t necessarily a problem.
Product IDs, postal codes, employee IDs, and similar identifiers often need to remain as text.
If you convert 00123 into a regular number, Excel stores it as 123 and the leading zeros are lost.
This can break a lookup when one table contains the text identifier 00123 and the other uses the numeric value 123.
Should You Convert Text to Numbers?
That depends on what the value represents.
For quantities, prices, and measurements, numeric values are usually necessary for calculations.
For identifiers, preserving the original text is often more important.
Long identifiers are another reason to be careful. Excel has a limit of 15 significant digits for numeric precision, so converting a long identifier to a number can permanently change its value.
Before converting anything, check the source format and how the value is meant to be used.
Both sides of a lookup should follow a consistent identifier format.
3. Watch for Hidden Characters and Similar-Looking Punctuation
Not every lookup problem is caused by spaces.
Sometimes the characters look almost identical but aren’t the same.
Consider the value in A5:
P–101
The character between P and 101 is an en dash, not the ordinary hyphen used in P-101.
They may look similar on screen, but they’re different characters.
If the source system specifies a standard hyphen for product IDs, you can correct this known difference with:
=SUBSTITUTE(A5,UNICHAR(8211),"-")
The result is:
P-101
This is a targeted correction. It replaces the en dash with a normal hyphen.
Can CLEAN Fix Hidden Characters?
Excel also has a CLEAN function:
=CLEAN(A5)
CLEAN is intended to remove certain nonprinting control characters. It doesn’t normalize every Unicode character or replace an en dash with a hyphen.
Don’t automatically strip all punctuation from product codes, either. Some systems use punctuation to distinguish one valid identifier from another.
For example, P-101 and P101 might represent different products.
Cleaning should follow the rules of the source data rather than simply removing everything that looks unusual.
4. Handle Dates Stored as Text Carefully
Dates introduce another kind of problem.
Look at the value in A6:
03/04/2026
Because we entered it as text, Excel isn’t treating it as a numeric date value.
You can confirm this with:
=ISNUMBER(A6)
The result is FALSE.
But there’s another question: what date does 03/04/2026 represent?
It could mean:
- 3 April 2026 in DD/MM/YYYY format.
- March 4, 2026 in MM/DD/YYYY format.
You need to know which format the source system uses before converting it.
Convert a Known DD/MM/YYYY Date
Suppose the source explicitly uses DD/MM/YYYY, with two-digit days and months and a four-digit year.
For this exact format, use:
=DATE(VALUE(RIGHT(A6,4)),VALUE(MID(A6,4,2)),VALUE(LEFT(A6,2)))
The formula extracts the year, month, and day separately before building an Excel date.
For our example, the result represents 3 April 2026.
Depending on the cell’s formatting, Excel may display the resulting date differently. You can apply a date number format to make it readable.
This isn’t a general-purpose date parser.
The DATE function can normalize out-of-range components instead of reporting every invalid date. If you’re importing important records, validate the day, month, year, and expected source format before accepting the converted value.
5. Check for Duplicate IDs After Cleaning
Cleaning data can solve lookup problems, but it can also introduce a different issue.
Imagine your source list contains these two values:
| Original product ID | Cleaned product ID |
|---|---|
P-101 |
P-101 |
P-101 |
P-101 |
P–102 |
P-102 |
The first two original values are different because one contains spaces.
After cleaning, they become identical.
If those rows contain different product information, your lookup may now have more than one possible match.
This is called a normalization collision.
Check for Duplicates Before Using Cleaned IDs
Suppose your cleaned product IDs are in column B, starting at B2.
You can check the number of occurrences of each cleaned ID using:
=COUNTIF($B$2:$B$100,B2)
A result greater than 1 means the cleaned ID appears more than once in the checked range.
For a larger dataset, you can also use conditional formatting or a separate duplicate-review column.
Don’t assume cleaning is safe just because the values now look consistent.
Review any duplicate keys before relying on them for XLOOKUP or other matching operations.
6. Test the Lookup Again
Once you’ve identified and corrected the relevant data issue, repeat the lookup.
Use at least three test cases:
- A known valid ID: Confirm that a product already matching correctly still returns the expected information.
- A previously problematic ID: Check that the cleaned version now finds the intended record.
- A genuinely missing ID: Make sure a nonexistent product is still reported as missing.
This last test matters because overly aggressive cleaning can cause previously distinct values to become the same.
A successful lookup isn’t enough on its own. You also need to know that it found the correct record.
Keep the Original Data
When importing information from another system, avoid immediately overwriting the source values.
Keep the original product IDs in one column and the cleaned versions in another.
For example:
| Original ID | Cleaned ID | Review |
|---|---|---|
P-101 |
P-101 |
Spaces removed |
P–102 |
P-102 |
Dash standardized |
00123 |
00123 |
Leading zeros preserved |
This gives you a simple record of what changed.
If a product starts matching the wrong record after cleaning, you can compare the original and cleaned values to investigate the cause.
For business-critical files, it’s also worth documenting the cleaning rules used during import.
Final Thoughts
When an Excel lookup fails, the formula isn’t always the problem.
The lookup value might contain an extra space, an invisible character, a different dash, or a text value that doesn’t match the source data type.
Functions such as LEN, TRIM, SUBSTITUTE, CLEAN, and ISNUMBER can help you investigate these differences.
The important part is to understand the data before changing it.
Keep your source values, apply cleaning rules deliberately, and check for duplicates afterward.
For more information about these functions, Microsoft’s official Excel documentation is a useful reference.
The examples in this article use fictional data. Test the formulas in your Excel version and regional settings before applying them to actual imported records.