INDEX MATCH Returning #N/A Error for Pasted Values

[Anthony Smith]

Hello,

I am really hoping someone can explain the reason for this issue. Whenever I try to copy numerical values that are stored as text and paste them onto another sheet, the #N/A error is returned when an INDEX MATCH is used on the pasted values. This happens even when I try to format the cells as text. The ways to correct this issue are to use the Text to Columns function and selecting text as the format (simple and easy) or edit each cell individually. 

Why doesn't formatting the cells as text work (it only does if you edit each cell)? What causes this issue?

Thanks. 

[#2]

index needs a value, text and numbers are different, or at least treated different. when copy & paste the text into your new file, use paste special values. if you need to keep any formulas, perhaps use the Value() within the formula to output a value so index can find it

[#3]

Many formulas in Excel treat the value formats differently. I'm trying to find out why the formula is still returning an error even though the formatting for both data ranges are the same. I'm assuming the cells aren't refreshing, and the formatting isn't being applied (as it works fine when the Text to Columns function is applied), but I'm not sure.