XMATCH Paradox

[mtarler]

So I was 'playing' with some formula ideas and tried this one and got some paradoxical results.  Can anyone explain what is happening?

=XMATCH(1:1,1:1,,-1)

So in this screenshot row 1 has some random numbers in random cells,

A2 has the formula: =XMATCH(1:1,1:1,,-1)

A3 is the same but using the older MATCH

A4 is similar but using SEARCH

So the paradox is that 

a) I could justify A2,C2, ..., O2, P2 returning 16384 as empty finds empty

b) I understand and expected R2, S2, U2, V2, .... and the rest returning #N/A

BUT why did it change from 16384 to #N/A

AND WHY is Q2 = 1

so the real paradox is that the value in Q2 is apparently ONLY found at A1, but A1 can be found at 16384, and the value at 16384 can't be found at all.

I hope someone can shed some light on this because I'm baffled

[#2]

@mtarler 

Perhaps kind of bug in the exact XMATCH.

=XMATCH(A1:SR1, A1:SR1,,-1) works correctly, but =XMATCH(A1:SS1, A1:SS1,,-1) gave the same error as in the sample. I.e. when we exceed 512 vector size.

By the way, =XMATCH(1:1, 1:1 ,1 ,-1)  have no such error and it looks like returns correct results.

[#3]

@mtarler 

Microsoft will probably say that XMATCH wasn't designed for such a situation, but it looks like the algorithm has a bug in which column Q plays a special role.

See the attached workbook, in which just one cell is filled, first in column A, then in column B, etc.

[#4]

wow both are interesting additions.
Hans, extending your pattern even further is even more interesting with the 'anomaly point' moving from Q to the right and then extending the 'count down' to match that column number. Then if you add another of the same character (you could replace the 1 with a 2 or something else) in a column further to the right then that will dictate the "count down" value but will start at the location of the first occurrence. It makes me think of buffer overflows used to inject code when hacking like somehow it is overflowing into a sequence code space (especially with the -1 in the step location). Makes me think I should stop playing before I overwrite something I really don't want to get overwritten.

[#5]

@mtarler 

That's great finding. We may discuss what is the nature of the bug and how to avoid it. However, I'd send a frown, let Joe to take care about this.

[#6]

@mtarler 

This works:

=XMATCH(INT(1:1),INT(1:1),,-1)

[#7]

@Patrick2788 

Interesting