Data types cause #N/A Error

I have two cells that appear the same, but return different data types.

Cell1: Displays "CPM". TYPE function returns value of 2 (text). This was manually entered as "CPM" (no leading or trailing spaces).
Cell2: Displays "CPM". TYPE function returns value of 1 (number). This value was arrived at through formula that refers to Cell1 and displays it.

Because of this, MATCH returns #N/A value.

I also tried a very simple validation formula: IF(Cell1=Cell2,"1","0"), and the resulting value is a #VALUE error (due to conflicting types, as noted above).

I've ALSO tried to simply change the format from "General" to "Text" for both cells, to no effect.

I've even tried using the TEXT function to convert Cell2 to text, but can't figure out a basic format that will display the value without modifying it in any way. I'm truly stumped. Can anyone shed any light here?

One of the two formulas below should work:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

The fact that 1 cell is manually entered and the other is the result of a calc will make no difference to excel, as excel sees what teh cell displays, not what it contains (formulas etc)

Not sure how you were using MATCH(), but this will return a cell position, and you dont need the "" in IF(Cell1=Cell2,"1","0"), just use IF(Cell1=Cell2,1,0). Using "" turns the 1 and 2 into text, so you might just as well have said IF(Cell1=Cell2,"YES","NO"). Likewise, if you are dealing with text in a cell, changing the format to text will also make no difference, excel already see's it as text

If your formulas are not seeing the 2 cells as containing teh same data when it looks like they do, check for leading/training/extra spaces.

Perhaps upload a sample workbook, and I can take a look and see whats going on for you?

I just realized that it looks like Cell2 is the problem (calculated value that returns TYPE 1 for number). In a new cell, I just entered "=Cell2", and it returned the #VALUE error.

And thanks for the response, Melvinrobb - unfortunately, neither of those worked. They both returned a #VALUE error (because of the problem I just discovered perhaps?).

The fact that 1 cell is manually entered and the other is the result of a calc will make no difference to excel, as excel sees what teh cell displays, not what it contains (formulas etc)

Not sure how you were using MATCH(), but this will return a cell position, and you dont need the "" in IF(Cell1=Cell2,"1","0"), just use IF(Cell1=Cell2,1,0). Using "" turns the 1 and 2 into text, so you might just as well have said IF(Cell1=Cell2,"YES","NO"). Likewise, if you are dealing with text in a cell, changing the format to text will also make no difference, excel already see's it as text

If your formulas are not seeing the 2 cells as containing teh same data when it looks like they do, check for leading/training/extra spaces.

Perhaps upload a sample workbook, and I can take a look and see whats going on for you?

I apologize, I wasn't very clear....

* The MATCH formula is returning a #N/A error.
* To discover why MATCH returned an error, I used the IF function to compare the values....this IF function didn't actually play a part in the MATCH function at all (I could have used "Yes" and "No"...I just needed the cell to display something so I knew if they were indeed equal).
* I've also checked for leading/trailing spaces...neither cell has them (I wish it were that easy).
* I'm working in a data-sensitive spreadsheet....I'll see if I can clean it up enough to keep the error without posting the critical data. Thanks for the help.


***Edit: I can't clean up the sheet enough to keep the error, and I can't post the data. =/ Sorry.

I just realized the problem: The Cell2 was a merged cell. I'm not sure why that made a difference, but I unmerged the cells and pointed at the one cell containing the value, and it works fine now.

Im happy th ath you managed to solve your question, I know how frustrating that can be. Just for future reference, try and avoid merged cells if at all possible, they cause all sorts of problems with formulas. If C2 was merged (say, with D2), maybe the formula was looking at D2 (which effectly doesnt exist now)

OR, more likely, when you referenced C2 in the MATCH(), it probably referenced C2:D2 for the search criteria - this requires a singe cell, not a range