Very strange issue with MATCH function.

EDIT: I realized my mistake, I didn't specify "Exact match" in the 'MATCH' formula. Problem solved. Original post below.

Hi.

I've found something I've never noticed before, which is that the MATCH function can easily be thrown off by the presence of text in certain cells.
In my example file, attached, you can see this clearly in Example 2 to 4.
In example 5, I changed the word "List" to "1.List", which seems to work.

I have many INDEX-MATCH formulas in my own report that are thrown off by the presence of these words (I absolutely need those words) & I have no idea why this error is happening, it makes no sense.

Does anyone understand the error & know a workaround or is this a bug?

I've attached my example file & a screenshot as well just in case.

You appear to be looking for an exact match, so should specify that in your formula. You omitted the third argument, so it defaults to:

MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order

The string "LIST" comes after your other values in a sort order, so your data is not in ascending order.

This formula explicitly specifies an exact match and does not produce the errors:

Formula:

Please Login or Register  to view this content.

I see you updated your post to SOLVED just as I was posting my solution. If you find your own solution, in addition to marking it SOLVED, we ask that you post that solution so when people find this thread in a search they can see the solution.