Using Excel Vlookup to match cells in another sheet with special characters

Question

I found that I could match cells from another sheet but the cells with additional special characters does not work as expected.

My formula in C2 = IFERROR(VLOOKUP(A2,Sheet2!A:M,3,false),"No Match") It works properly until I hit cells with special characters.

Example: In Sheet 1

A     B    C
ABC  XXX   0
ABC@ XXX   **No Match**

In Sheet 2

M
ABC
ABC@

The formula will need to change for it to match cells in Sheet 2.

I need the results to reflect correctly in Sheet 1.

J.Mapz J.Mapz · Accepted Answer · 2018-09-25T12:35:42

The formula should work just fine. Maybe there's an additional space on either the lookup value or on the lookup range.

"ABC@" is not equal to "ABC@ " (notice the space on the second value). Other variations of such exist.

You can test out the formula by copying the "ABC@" from sheet1 to the "ABC@" of sheet2, and the formula should correctly return your data.