Excel Match Bug in a sparse range with duplicate keys

Question

Data in TheRange is {1,"",1,"",1,"",1,"",2}

=Match(2, TheRange, 1) returns 9 as expected

=Match(1.5, TheRange, 1) returns 7 as expected

=Match(1, TheRange, 1) returns 5 which is not expected

Anyone come across this ? Anyone got a fix?

Additionally if I use Worksheet.Function.Match in VBA I get more unexpected results.

Answer 1

If you specify 1 for the "match_type" argument (the 3rd argument to MATCH), then Excel expects the array to be sorted. Apparently, Excel does a binary search for the value - starting in the middle and finding the middle value (which is the 5th value in your case) when searching for 1.

If you specify 0 for "match_type", you will get what you expect - at least for this case. See the documentation for the MATCH function for more info.

Answer 2

One way to solve this is turn the blanks to errors.

Try these formula:

=MATCH(1/(2),1/$A$1:$A$9,-1)
=MATCH(1/(1.5),1/$A$1:$A$9,-1)
=MATCH(1/(1),1/$A$1:$A$9,-1)

These are array formulas so when you type them in you must confirm them with "ctrl+shift+enter" instead of just "enter".