Index-Match function not finding matches with newly pasted values

I'm using an index-match formula, see here:

Please Login or Register  to view this content.

What it does is it looks in cell A26 in lets say Sheet 1 (which values are changed by a drop-down selection), searches for ALL matches of the company name in A26 within say Sheet 2 column 1. Once it finds those several matches, it returns the multiple values for that one corresponding company in Sheet 2 Column 2 due to the formula being an array. It's been working just fine, but the data needs to be updated every month. With my most recent data, the formula is still working for all the PREVIOUSLY existing companies, but any NEW company name that is added to sheet 2 column 1, they are not correctly identifying the match in A26 although the names are clearly matching the desired lookup value in cell A26. I'm pasting new data in all at once, and I'm pasting Values Only.

Important note: I've noted that although the newer companies aren't being automatically matched when pasted, if I DELETE the pasted newer company names and simply type them in exactly the same, the formula then DOES properly return the values. I don't wish to have to scan through all my data to pick out the newer companies and retype their names for the index-match to return the values properly, I'd like it to be matched right away when it's pasted in. Any help on this would be greatly appreciated!!! Thanks!

Mark

It sounds like your pasted values are not an exact match. They either have leading or trailing spaces or some other different invisible character. Can you upload an example? (Go Advanced>Manage Attachments)?

Try and see if this works for you
Select the column
Data> "Text To Columns"
Select Fixed Width
Next (remove any breaks if there are any)
Next
Finish
See if the spaces are gone

Glad I could help.