Index Match #NA error

[marinamos]

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!!

[#2]

@marinamos 

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

[#3]

@HansVogelaar Here you go!

https://docs.google.com/spreadsheets/d/1H8rV29DZKWEWR9NXHNrzzwUI_c9pjxyA/edit?usp=sharing&ouid=11275...

thanks a lot

[#4]

@marinamos 

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

[#5]

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

[#6]

its a drive link, it does say docs.google 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

[#7]

@marinamos 

I still get Access Denied.

[#8]

@HansVogelaar sorry for that, I gave full access now, maybe now it works? 😕 

https://docs.google.com/spreadsheets/d/1H8rV29DZKWEWR9NXHNrzzwUI_c9pjxyA/edit?usp=sharing&ouid=11275...

Thanks a lot

[#9]

@marinamos 

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

[#10 · Accepted solution]

@marinamos 

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

[#11]

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