Why match doesnt work

But I sure as hell don't want to manually input all my data. So my question is 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. Text can be in any of the constant number, date, or time formats recognized by Microsoft Excel.

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. 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:. 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. I had this issue.

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. 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 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. They were "General". Just formatting didn't work, and I was able to recreate it with previously saved versions. 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! A 30 second fix that I spent a whole hour on.

Sounds like there might be a string length issue. 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 you had copy and paste your data into excel and sorted, i find that the result can become falsely reported as matched. Before comparing cells or using Application.

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:. 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? For more information on array formulas, see Guidelines and examples of array formulas. Then try it again. You can always ask an expert in the Excel Tech Community or get support in the Answers community. INDEX function.

MATCH function. Overview of formulas in Excel. How to avoid broken formulas. Detect errors in formulas. All Excel functions alphabetical. All Excel functions by category.

Need more help? Expand your skills. Get new features first.