Excel Match() returns a value, although the lookup value is not in the array

Question

As you can see in the screenshot, MATCH() returns a value where it shouldn't.

? The lookup value is referenced to an array (in this case: $AB$18:$AB$22 -> the red box in the screenshot)

?For some values, MATCH() returns, #N/A, which is perfectly fine, as the lookup value is not in the array

? For others, it returns the correct positional value (e.g. for 1604, it returns 4, which is the correct position)

? Problem: Sometimes, Match returns a Value, where there should be #N/A instead

? For example: See the line, where it says "1705", it returns "5", which is not true.

What can I do, to make this right?

Additinal Info:

• For specific reasons, the numbers are stored as text, so I tried a workaround with the TEXT() function, which usually works fine.

question from:https://stackoverflow.com/questions/65885052/excel-match-returns-a-value-although-the-lookup-value-is-not-in-the-array

Answer 1

Waitting for answers