VLOOKUP Not Working? Fix Excel Formatting Mismatches

Yes—VLOOKUP can fail when a lookup value and the matching-looking cell are stored differently, such as the number 1042 versus the text value “1042.” Number formatting alone usually does not cause the failure; check the underlying data type, spaces, exact-match setting, and lookup range. With IDs in A2:A100, results in B2:B100, and lookup ID 1042 in E2, =VLOOKUP(E2,$A$2:$B$100,2,FALSE) returns the value from column B on the exact matching row—for example, North if that row’s result is North.

What formatting changes—and what does not

Excel separates a cell’s stored value from how that value appears. A number such as 1042 can be displayed with commas, as currency, or with a date-like format, while its stored value remains numeric. Changing the number format through Home > Number does not convert text into a number or change a number into text. As a result, two cells can look alike on screen but still be different lookup keys.

For instance, a cell containing the number 1042 and a cell containing the text characters “1042” may look identical with General formatting. An exact VLOOKUP can treat them as different. Conversely, a number formatted to display as 001042 still has the numeric value 1042; the leading zeros are a display choice, not part of the stored number. This distinction matters especially for product codes, postal codes, employee IDs, and account references.

So “formatting” is a common shorthand for several separate problems. The visible number format itself is rarely the culprit. Data type, hidden characters, and inconsistent conventions are more likely.

How VLOOKUP makes a match

VLOOKUP searches down the first column of its table range for the lookup value, then returns a value from a column to the right on the matching row. Its fourth argument controls the match mode. Use FALSE, or its equivalent 0, for an exact match when looking up identifiers or specific values. If you omit this argument or use TRUE, VLOOKUP performs an approximate match and generally expects the first column to be sorted in ascending order. That can produce a plausible but incorrect result rather than an obvious error.

The lookup column must also be the leftmost column in the range. In the example above, column A contains the keys and column B contains the results. If the keys are actually in B and the return values are in A, VLOOKUP cannot search left from B. A formatting cleanup will not fix an incorrectly arranged range.

Exact matching is not case-sensitive: for example, VLOOKUP does not distinguish between “abc” and “ABC.” It does, however, depend on the value being a compatible match. Extra spaces, text-versus-number differences, and different date representations can prevent a match.

Diagnose the mismatch before changing data

Start by checking the formula bar for both the lookup key and the supposed matching cell. Then compare their types. With the lookup value in E2 and a possible matching key in A2, =ISNUMBER(E2) tells you whether E2 is numeric, while =ISNUMBER(A2) checks A2. TRUE means the cell contains a number; FALSE means it is not numeric, though it could be text, a date-like text value, or another type. If one result is TRUE and the other is FALSE, investigate a type mismatch.

Next, inspect the formula itself. Confirm that the range begins with the key column, includes the return column, and uses the correct column number. The column number is counted from the left edge of the selected range, not from the worksheet’s column letters. For example, if the range begins in D, the first column in the range is 1, even though it is worksheet column D.

#N/A: Excel did not find a match under the formula’s current conditions. Check the key, data types, spaces, and exact-match argument.
A wrong but plausible result: Check whether the formula is using approximate matching, whether its range shifted, or whether the return-column number is wrong.
#REF!: The return-column number may be larger than the number of columns in the selected range.
A correct result for one row but not another: Compare the failing row’s actual contents with a working key; inconsistent imports often affect only some records.

Click a cell and press F2, then inspect the content and press Esc to leave it unchanged. On a Mac, use Control+U to edit the active cell in the cell, then press Esc. You can also use the formula bar to inspect a long value without changing it. Do not assume that identical alignment or font means identical data.

Fix numbers stored as text

Choose one consistent representation for both sides of the lookup. For quantities or values used in arithmetic, converting numeric text to real numbers is often appropriate. Select the affected cells and look for Excel’s warning indicator; where available, choose Convert to Number. You can also use Data > Text to Columns and select Finish to convert suitable text-formatted numbers. Check a sample afterward, because imported identifiers may need to remain text.

A helper column is a cautious way to test a conversion. With a number-like value in A2, =VALUE(A2) converts text that Excel recognizes as a number into a numeric value. If A2 contains the text 1042, the result is the number 1042. Fill the formula down, then use that cleaned column consistently as the lookup column and lookup value. VALUE will not make arbitrary labels numeric, and it may not be suitable for identifiers whose zeros or exact character pattern matter.

For identifiers, text may be the better standard. If one source has 00123 and another has 123, decide whether those are supposed to identify the same item. If the zeros are meaningful, preserve them as text and make both lookup columns follow the same fixed-width convention. Simply applying a custom number format that displays five digits does not make the zeros part of the underlying numeric value.

Remove ordinary and hidden spaces

Imported data can include leading spaces, trailing spaces, repeated spaces, or nonbreaking spaces copied from web pages. These characters may be difficult to notice, but an exact lookup can treat the strings as different. A helper column is safer than editing source data directly because you can compare the cleaned result before replacing anything.

With text in A2, =TRIM(CLEAN(A2)) removes leading and trailing ordinary spaces, reduces repeated ordinary spaces between words to single spaces, and removes certain nonprinting characters. If A2 contains three spaces before and after AB-17, the result is AB-17. CLEAN does not remove every possible Unicode or invisible character, so a stubborn mismatch may need further investigation.

For a common nonbreaking space, use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) with the original text in A2. SUBSTITUTE changes character 160 to an ordinary space, and TRIM then removes extra ordinary spaces. If the source contains other unusual characters, identify them before applying a broader replacement; removing punctuation or meaningful characters from IDs could create accidental matches.

Apply the same cleanup logic to both the lookup column and the value being searched. Cleaning only one side can leave the two values inconsistent. After confirming the helper results, you can copy and paste values into a dedicated clean-data column, or keep the helper formulas as part of a repeatable import process.

Dates, leading zeros, and other look-alikes

Excel stores real dates as serial numbers and displays them according to a date format. A date entered as text, such as a string imported from another system, is not necessarily the same stored value as a real Excel date. If a date lookup fails, check whether both cells are genuine dates and whether their underlying values correspond. Changing a text date’s number format to a date format does not, by itself, convert the text into an Excel date.

Leading zeros deserve special care. If 00123 is an account code, it may be a different identifier from 123 even though converting both to numbers makes them equal. Keep codes as text when every character is significant. If the business rule says codes always have a fixed width, normalize both sources to that width rather than relying on a cell’s visual number format.

Other potential differences include hyphens versus en dashes, straight versus curly apostrophes, and characters copied from PDFs or websites. When values look identical but do not match, compare their lengths and inspect the source data. Avoid broad search-and-replace operations until you know which characters are safe to change.

Make the formula reliable

Once the data is consistent, use an explicit exact-match argument for ordinary lookups. Confirm that the lookup value is in the correct type and that the lookup range is anchored if you plan to copy the formula down. Dollar signs in $A$2:$B$100 keep that range fixed as the formula is filled into other rows; they do not change the lookup behavior.

Also verify the return-column index after editing a range. If the range is A:B, 2 returns the second column of that range. If the range changes to A:C and the desired result is in C, use 3. When a table grows over time, an Excel Table can make a lookup range easier to maintain, but it will not resolve mismatched text and numeric keys by itself.

For Microsoft 365 and Excel 2021 or later, XLOOKUP is an alternative that can search in either direction and uses exact matching by default. It still cannot make genuinely different key values equivalent automatically. If one source stores an ID as text and another as a number, standardize the data before switching functions. The Mac and web versions may present different interface details, but the underlying type-matching issue is the same.

Frequently Asked Questions

Does cell color or font formatting stop VLOOKUP from working?
Usually not. Fill color, font, borders, and ordinary number display formats do not determine whether the underlying lookup values match. Text-versus-number differences or hidden characters are more likely causes.

Why does VLOOKUP return #N/A when the values look identical?
The cells may contain different data types, extra spaces, nonbreaking spaces, or different characters. Check the actual contents and compare whether each key is text or numeric. Also make sure the formula uses FALSE or 0 for an exact match.

Should I format both columns as Text?
Only if the values are identifiers that should be treated as character strings. Applying Text formatting does not reliably convert existing numbers into text; convert or rebuild the values consistently, then verify the results. For quantities used in calculations, numeric values are usually more suitable.

Will changing the column to Number fix a text-number mismatch?
Not necessarily. A number format changes how numeric values appear; it does not always convert text values already in cells. Use a conversion method such as Convert to Number, Text to Columns, or a tested VALUE helper formula, then check both sides of the lookup.

Why does VLOOKUP return the wrong row instead of #N/A?
Check for approximate matching. If the fourth argument is omitted or TRUE, VLOOKUP may return an approximate result, which can be wrong when the key column is unsorted. Use FALSE or 0 when you need an exact identifier match.

Can I use VLOOKUP when the lookup column is to the right of the result?
No. VLOOKUP searches the first column of its range and returns from a column to its right. Rearrange the range or use a function such as XLOOKUP in supported Excel versions. Neither approach removes the need to make the keys consistent.

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.