Excel-MATCH function doesn't work on pasted values
Andrew Mclaughlin 
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.
214 Answers
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 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.
2The 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...
In my case, I simply deleted the spaces in the pasted cells and the contents finally matched.
2I 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.
1I 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.
1I 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!
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
- Copy column (A:A) and Paste to Notepad
- After Pasted to Notepad, Ctrl+A and Ctrl+X
Go Back to Excel
- 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.
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. :-(
Sounds like there might be a string length issue. Try =MATCH(TRIM(B1), A:A, 0). If that still isn't working, trying applying TRIM() to everything in both columns, then copy the result back into the source column. Your match function should work after that.
If both columns are already in the same format and you are still getting #N/A, try sorting both the columns in the same order. This works 100% for me so far. What I find interesting is that when the columns are unsorted and the matched row displayed as #N/A, if you update the formula with specific lookup position, it would match. If you had copy and paste your data into excel and sorted, i find that the result can become falsely reported as matched. Best to use INDEX and MATCH functions.
I had the same issue and I just solved it.
Before comparing cells or using Application.Match in VBA you have to convert BOTH cells to the same format.
One of my ranges could have either alpha-numeric strings OR integer values, so I converted everything to Text format type using the following code:
Range("A1").NumberFormat = "@" 'The @ sign converts to Text type
For all other cell formatting types, this web page is a great reference:
