excel-formulavlookup

Excel VLOOKUP Doesn't Recognize Identical Cells

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)

Solution

  • 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 ?