Index Match #NA error

Copper Contributor



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!








Array sheet



Thanks a lot in advance!!


10 Replies


Please make a sample workbook (without sensitive data) available through OneDrive, Google Drive, Dropbox or similar.


Please share the workbook - I currently get "Access Denied".

@marinamos  posted:


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, not


I prefer an "unaware" file-sharing website like or  Then I'm confident that the file is not changed by "smart" software.

its a drive link, it does say but when getting access you can download the file! I tried the link with other people and it should work correctly! please let me know if you get access to it? thanks a lot


I still get Access Denied.

@Hans Vogelaar sorry for that, I gave full access now, maybe now it works? :\


Thanks a lot


Yes, it worked now. I'll take a look.

best response confirmed by marinamos (Copper Contributor)


=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.

Thank you very much Hans!! I will use trim going forward, sooo useful, big big thanks