INDEX MATCH Returning #N/A Error for Pasted Values

Anthony Smith



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?



2 Replies
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

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. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
50 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
32 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
15 Replies
Dev channel update to 80.0.355.1 is live
josh_bodner in Discussions on
67 Replies