Home

INDEX MATCH Returning #N/A Error for Pasted Values

Anthony Smith
Contributor

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 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
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies