Excel MATCH() function always giving #N/A

Question

Super simple match, looks like this:

=MATCH("Team", A1:A25,0)

The data in A1:A25 is imported from a text file. The cells are formatted as text.

If I go into the column and manually type "Team" into the cell that already says "Team", then it will work. But unless I manually type it in, it says #N/A.

What is wrong with this?

Answer 1

One of the problems could be, that you have unwanted empty characters (I mean space) either before or after the word (so it is 'Team ' instead of 'Team'). Dirty workaround would be trimming text such as following:

{=MATCH("Team", TRIM(A1:A25),0)}

(Please make sure that you enter it as Array formula with Ctrl + Shift + Enter)

Answer 2

Without seeing the data in A1:A25, here's what I would do as a test.

Insert a column B next to column A.

Cntl-Spacebar to highlight all of column B.

Make sure the field time is set to General, not text.

Then run a formula in column b: =trim(A1) and paste it down.

This will make sure the field is trimmed (i.e. there are no blank spaces at the end of the word).

This may fix your issue.