Formula returning the incorrect value

[dantegarzia]

I am trying to lookup the value in cell A2 in column H and return a Yes if that value is in Column H and a No if it is not. I have tried using the below formulas, but it keeps returning "No" on values I can clearly see are in Column H. I have tried to change the formatting of both columns, but still no luck.

=IF(COUNTIF(H:H,A2)>0,"Yes","No")

=IF(ISNUMBER(MATCH(A2,H:H,0)),"Yes","No")

[#2]

@dantegarzia Can you show what you have in A2 and column H? Looks may deceive and what you see as same values may be seen by Excel as different values.

[#3]

@Riny_van_Eekelen  This is what the data looks like using the IF COUNTIF formula I mentioned. As you can see it returns no, even though that value is in column H

[#4]

@dantegarzia Enter this in an empty cell:

=A10=H14 (assuming these are the cell that you marked in yellow)

What do you get?

[#5]

It returned FALSE when I put that formula into a blank cell referencing what I highlighted in yellow.

[#6]

@dantegarzia Then they don't contain the same values. would need to guess without seeing the file. Can you upload it to Onedrive or similar and post a link here?

[#7]

I am unable to upload the spreadsheet, but I do understand what you are saying. I just manually typed in the number into both cells and it returned the correct value. Could it be from copying each set of data from a different spreadsheet as that is where it originated and I moved it to a new sheet to manipulate.

[#8]

@dantegarzia 

Yes! Copying from different sources can cause such side effects. Especially when the copy and paste from the web is involved at some stage.

[#9]

Is there a type of special paste that would help to solve this issue or would working with the data in the original spreadsheet be the best option for this? I was hoping to avoid the original spreadsheet as it contains thousands of lines and is extremely slow.

[#10]

@dantegarzia Can't tell without knowing what's causing this. Sorry.

[#11]

@dantegarzia 

Check with LEN() whether both values have the same length.