Csdrhrt

I'm trying to find the positions of the values(text) in one column in another column. I ran the function: =MATCH(B1, A:A, 0) and I get a #N/A result. But this result is incorrect...as I clearly see the value of B1 in column A.

I thought the issue might be with the fact that I pasted the cells into the sheet. So I did a test run where I manually inputed the values in the cells and then ran the function. Result: It worked.... But I sure as hell don't want to manually input all my data.

So my question is...how do I fix this? I've tried pasting the values in all sorts of formats and still no luck. Maybe this is not the issue? I do not know. Suggestions will be greatly appreciated.

In my experience, this occurs because you're trying to match cells with two different formats. For example, when you copy and paste data into column A, it may be pasted as Text format. If B1 is numeric and A:A are text cells, even if the content is identical and there are no superfluous spaces or other invisible characters, the match will still return #N/A.

You probably know how to change the cell format, but I'll describe it for the sake of completeness. In the Home tab of the Ribbon, click here:

and change the formats of each group of cells so that they match.

If you're matching numbers, try using the "VALUE" function.

For instance =VALUE(A1) will return (the Number) 100 if cell A1 is TEXT formatted, and contains 100, or 100 with a trailing or leading space (perhaps multiple, I didn't try)

It can be really helpful when, like mentioned above, formatting is stopping matches or lookups.

This is what Excel 2007 help says about it:

"Converts a text string that represents a number, to a number"
Text can be in any of the constant number, date, or time formats recognized by Microsoft Excel. If text is not in one of these formats, VALUE returns the #VALUE! error value.

In my case, I replaced spaces with blanks in the pasted cells and the contents finally matched.

I had this issue. In my case the tilde character (~) broke the MATCH function, even with pasted values. There may be other special characters that do this as well.

I had a similar problem using the match function between two different sheets. Cells were correctly formatted and had no extra spaces. Weirder still, the match function would throw the #N/A sometimes, but other times it would provide a number, but with the wrong row number.

Solution: I re-ordered both sheets by the columns I was matching and poof! All fixed. Couldn't tell you why, though.

I've managed to solve this, without really understanding the cause... but it seems to be some kind of formatting related thing.

What you should do is copy the matched column of each sheet to Notepad, and then cut and paste back. This will get rid of the problem. Hope it helps!

I too killed an hour on this. The notepad trick DID work, but first I had to format the respective columns to be "Text". They were "General". Just formatting didn't work, and I was able to recreate it with previously saved versions.

I had a similar problem comparing "Time" fields, it turned out one of the columns actually contained date & time while the other was just time (both were from CSV files). They were both formatted to show TIME only so I didn't notice at first but when I converted them both to Time - voala!