Excel Match Bug in a Sparse Range with Duplicate Keys

The 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) ret开发者_高级运维urns 5 which is not expected.

Has anyone come across this ? Does anyone have a fix?

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

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.

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".