Confused wrote...
The data is arranged in 2 excel spreadsheets in columns. The names are
written the same way and both formatted as text. I hve tried formatting both
columns same way. same problem. If I retype both names in the two
spreadsheets the correct info is returned.
....

Formatting is COMPLETELY IRRELEVANT. Formatting changes only how cells
are displayed, not the cells' underlying values. Formulas work only on
the underlying values which formatting doesn't affect.