Weird MATCH error

[annefensie]

I am trying to connect data from one excel file to another, using INDEX and MATCH. The problem is with the MATCH function. When I try just the MATCH function, I get #N/A as if it can't find the value I am looking up. The values are CIP codes, so they are numbers but without any actual numerical value. I've tried formatting both sets of values in the two sheets as text and as number without success. What has worked is going into each individual cell and clicking Enter. I thought maybe there were some hidden characters in there, so I tried CLEAN and TRIM, too, without success. I have several more sheets to go with hundreds of rows of data, and I don't want to open each one, click in each cell, and hit Enter, but I will if no one can help me figure this out. Why would that even work??

[#2]

@annefensie 

If text format was applied to the cell, you enter 123 into it, it will be text "123". If to apply number format to the cell it still will be text "123" in the cell. Applying of format doesn't convert text to number, you shall to re-enter the value. F2 -> Enter and now you have number value.

Text To Columns is most common way to re-enter values at once if the they are in one column. Step-by-step instruction is here How to Refresh Cell Data After Applying Number Formatting in Excel (groovypost.com)

[#3]

Or you can use numbervalue function in your formula to change texts to numbers on the fly)
Artur

[#4]

@artup720 

If only you don't use PivotTable or like for the analysis.