Excel VLOOKUP Doesn't Recognize Identical Cells - Stack Overflow

admin管理员组

文章数量:1431037

I'm experiencing an issue with Excel's VLOOKUP function returning "N/A" despite cells looking identical. Below is an example of my sheet

The formula in cell B1 is:

=VLOOKUP(A1, A4, 1, FALSE)

Both cells A1 and A4 appear to be the same, and even when I copy the content from A1 to A4, the result still shows "N/A". Here's the content of the problematic cell:

cell A1= "AR-HE-HDC100-1:0.1~HDP301#Power supply DC"

To test if values in cells A1 and A4 are the same, I put formula in cell C1:'=A1=A4'. Result of that formula is TRUE, which proves that cells have the same content.

I'm experiencing an issue with Excel's VLOOKUP function returning "N/A" despite cells looking identical. Below is an example of my sheet

The formula in cell B1 is:

=VLOOKUP(A1, A4, 1, FALSE)

Both cells A1 and A4 appear to be the same, and even when I copy the content from A1 to A4, the result still shows "N/A". Here's the content of the problematic cell:

cell A1= "AR-HE-HDC100-1:0.1~HDP301#Power supply DC"

To test if values in cells A1 and A4 are the same, I put formula in cell C1:'=A1=A4'. Result of that formula is TRUE, which proves that cells have the same content.

• excel-formula
• vlookup

Share Improve this question Follow edited Nov 19, 2024 at 11:34 knez asked Nov 19, 2024 at 9:46 knezknez 5366 bronze badges

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 2

A quick experiment in Excel 2023 confirms your unlikely observation.

The MRE that elicits this bad behaviour in Excel is this minimal example

"~"
"=VLOOKUP(A1,A4:A5,1,FALSE)"
3
"=A1"

The 3 is in as a placeholder to keep indexing the same as in the OP's question and I changed the range to be two cells in case it was objecting to a range of length 1.

Any string containing the twiddle character ~ escape becomes unmatchable.

Based on a quick test at the keyboard I can see no other special characters that cause a problem. They all seem to work OK but ~ has a special meaning in Excel match patterns and so causes trouble if it appears in data.

See using wildcard escape characters in Excel searches

Doctoring the search string for lookup so that any solitary "~" characters are replaced by "~~" should fix your problem. "*" or "?" being a wildcard match may also cause you trouble if they appear in your search string

VLOOKUP function - Microsoft Support

Use wildcard characters

If range_lookup is FALSE and lookup_value is text, you can use the wildcard characters—the question mark (?) and asterisk (*)—in lookup_value. A question mark matches any single character. An asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) in front of the character.

As Martin Brown also pointed out,

Using wildcard characters in searches - Microsoft Support

~ (tilde) followed by ?, *, or ~

so escaping ~ might help:

=VLOOKUP(SUBSTITUTE(A1,"~","~~"), A4, 1, FALSE)

---

You may want to check whether your lookup values include * or ?

I think the problem is the character "~" which Excel seems to treat wrong for your formula.

To handle the ~ character correctly in your VLOOKUP formula, you need to "escape" it by adding another ~ in front of it "~~". This tells Excel to treat the ~ as a literal character and not as a special character.

Or replace it with an empty string or another character.

本文标签： Excel VLOOKUP Doesn39t Recognize Identical CellsStack Overflow