Excel-MATCH function doesn't work on pasted values

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

The solution I just did for this exact problem is pretty embarrassingly low-brow, but it worked:

Just multiply your "numbers" (which Excel still thinks are text, somehow) by 1 (or divide by 1, or add 0, or whatever) in another column.

Now Excel knows they are numbers.

I wasted so much time on this...

Answer 5

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!

Answer 6

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.

Answer 7

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!

Answer 8

You most likely have spaces or special characters you can't see.(Format Issue)

List/ Column you're searching your data in (A:A)-In this Scenario

1. Copy column (A:A) and Paste to Notepad
2. After Pasted to Notepad, Ctrl+A and Ctrl+X

Go Back to Excel

1. Ctrl+V to on the Column

This gets rid of all the spaces in between character/ fixes the formatting issue.

-Not the nicest way to solve this problem, but when you have thousands of rows to search through, it's the easiest solution.

Answer 9

in case this helps anyone (or me in a year's time when I forget how to solve it again!!), when I copied and pasted last years database to make a new one, all of my index matches looked as if they were working (i.e. they had an answer in them, but the incorrect one), but they weren't.

I forgot that I had names in formulas on the matched sheets and I had not updated these to reflect the name of the new database. So, go to Formulas, Name Manager and check the names in here and edit any that are incorrect. Then apply Names across the database, and Voila! it all now works. A 30 second fix that I spent a whole hour on. :-(

Answer 10

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.

Answer 11

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.