Search code examples
excelexcel-formulaworksheet-function

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) returns 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.

Solution

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