Match Function Returning NA and Incorrect Values

[Fugdkn]

I am extremely confused with what is going on in this file I was using. I understand the match function tells you the relative position of something in some given range, but the values it is giving me here seem totally wild. I get NAs sometimes when I change the text on the left, and other times I just get the wrong position (sometimes the right position). I am honestly beyond confused at this point as to what could possibly be going on (there is no consistency - I presume there must be something modified about the cells that I am not seeing). Please help, I beg.

[Fugdkn]

Just realised that apparently match has to be used on sorted data? I never knew that was the case. Is there anyway of getting around this (should I try using the sort function)?

[MrShorty]

MATCH() with an omitted 3rd argument has to be used on sorted data. If you put 0 into the 3rd argument, then it will find an exact match and the data do not need to be sorted. =MATCH(A4,$A$4:$A$7,0)

MATCH() help file: https://support.microsoft.com/en-us/...rs=en-us&ad=us

[FDibbins]

Thats because you did not add the last argument, which tells excel if you are working with a sorted list (1) to find a approximate match, or an unsorted list (0) to find an exact match.

	A	B	C
3	Code
4	Swam	4	1
5	OPS	2	2
6	Hi	#N/A	3
7	PBC03A	4	4

Your formula...
B4=MATCH(A4,$A$4:$A$7)

My formula...
C4=MATCH(A4,$A$4:$A$7,0)

[Fugdkn]

Thank you so much! I think using xlookup so much and not having to worry about exact or non-exact matches has made me lazy. I am surprised that the match defaults the way it does. Thanks again though!!!

[FDibbins]

Happy to help and thanks for the feedback