4

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.

2
  • I think that there are spaces (or other invisible characters) in the pasted text, in which case using =MATCH("*"&B1&"*", A:A, 0) should give a match. But that means that the text B1 can be anywhere in the cells you're looking up (* is a wildcard in excel to mean any number of any characters).
    – Jerry
    Sep 20, 2013 at 17:45
  • Check out this question. superuser.com/q/1549376/160304 Jun 23, 2020 at 11:42

14 Answers 14

4

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.

3

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:

home tab with freehand circles

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

2
  • I think you're right about the mismatch John but it's not always easy to change formats, e.g. if you have text formatted numbers you can't simply change to a numeric format (or actually you can make the change but it doesn't work) - Text to columns functionality can be used to change the format of a whole column Sep 21, 2013 at 10:59
  • 1
    You might be able to copy and Paste Special into the same spot and choose the format there too (e.g., paste values)
    – beroe
    Sep 21, 2013 at 17:10
2

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...

2

In my case, I simply deleted the spaces in the pasted cells and the contents finally matched.

2
  • 3
    what's the difference between a space and a blank? Dec 11, 2017 at 11:43
  • @Pierre.Vriens a blank is nothing, not even a space
    – pateksan
    Mar 5, 2021 at 15:43
0

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.

1
  • The question really describes a different situation. There is no problem with the characters when manually entered.
    – fixer1234
    Apr 17, 2015 at 17:12
0

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.

0

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!

0

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.

1
  • 2
    This answer appears to be commentary. Commentary should never be submitted as an answer to a question. You should edit this answer, and provide specific details, how the author solves their problem. An answer, is not the proper place, to get help with your own problems. Your lack of reputation,and your inability to submit comments, isn't a valid reason to submit comments as an answer. Please consider modifying your answer so it answers the author's question.
    – Ramhound
    Jun 26, 2017 at 17:54
0

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!

0

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.

0

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. :-(

0

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.

0

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.

0

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:

https://www.excelhowto.com/macros/formatting-a-range-of-cells-in-excel-vba/

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .