3

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.

![![enter image description here ![[![enter image description here](https://i.sstatic.net/TfWvFSJj.png)](https://i.sstatic.net/TfWvFSJj.png)

Improve this question

3 Answers 3

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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)

Result

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

Improve this answer

Comments

1

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.