Forum Discussion
Solved
Index Match #NA error
Hi!
Hope you can help me...
I use office excel 2019 (so use ctrl+shift+enter to enter my index match formula).
I have 3 array sheets as reference and 25 product sheets with the exact same format and design where I added my formulas. Each product sheet is for a different product, and pulls the product data from the correct array sheet depending on the data needed. The idea was to automate creating those admin documents.
It is all working properly except for 3 sheets... they are all 3 displaying the same N/A error for all the index/match formulas I have used (the other types of formula work). I have checked the array sheets and the format is the same as for the other products sheets, so I don't understand why it would work for all the sheets but those 3!
Does anyone have any idea why it would not pull properly for some sheets only, and what would be the solution? 😕
See below screenshot for unsuccessful sheet and successful sheet example, as well as the array sheet I am pulling from for the exact formula selected on both successful and unsuccessful examples.
Sorry, I would have liked to upload my file here but I don't have the option!
Unsuccessful
Successful
Array sheet
Thanks a lot in advance!!
=LEFT(T5,9) returns "SK386IVO ", with a space at the end. The POs sheet has "SK386IVO" without a space.
If you change the formula to =TRIM(LEFT(T5,9)) to remove the trailing space, it'll work.
Please make a sample workbook (without sensitive data) available through OneDrive, Google Drive, Dropbox or similar.
marinamos posted: https://docs.google.com/spreadsheets/....
Google Sheets is not an Excel. I don't believe an Excel file uploaded to Google Sheets is the original Excel file.
If you "must" use Google for file-sharing, upload to drive.google.com, not docs.google.com.
I prefer an "unaware" file-sharing website like box.net/files or dropbox.com. Then I'm confident that the file is not changed by "smart" software.
