MATCH using -1 as type

[AJNandi]

MATCH(Searchcriterion; Lookuparray; Type)

The match function specifies a type as the 3rd argument. If you use "1", HF works correctly to match the lower bound. However, if you use "-1" it should return the upper bound. This is the behavior of Sheets and Excel. Hyperformula throws an error.

MATCH working correctly

test("Match with negative match", () => {
  const engine = HyperFormula.buildFromArray(
    [["=MATCH(203, A2:A5, 1)"], ["100"], ["200"], ["300"], ["400"], ["500"]],
    {
      licenseKey: "gpl-v3",
    }
  );

  expect(engine.getCellValue({ sheet: 0, row: 0, col: 0 })).toEqual(2);
});

MATCH throwing error

Error: Value of the formula cell is not computed.

test("Match with negative match", () => {
  const engine = HyperFormula.buildFromArray(
    [["=MATCH(203, A2:A5, -1)"], ["500"], ["400"], ["300"], ["200"], ["100"]],
    {
      licenseKey: "gpl-v3",
    }
  );

  expect(engine.getCellValue({ sheet: 0, row: 0, col: 0 })).toEqual(3);
});

I should note, this assumes that the data is ordered correctly. We should probably update the documentation to mention that requirement. When the data is ordered incorrectly, Google sheets will return the largest value, whereas hyperformula returns the same value whether its -1 or 1. We may want to update the runtime differences docs to reflect that difference if it is intended. See below for that example. You can see Google sheets explain the data ordering assumption here: https://support.google.com/docs/answer/3093378

test("Match with negative match", () => {
  const engine = HyperFormula.buildFromArray(
    [["=MATCH(203, A2:A5, -1)"], ["100"], ["200"], ["300"], ["400"], ["500"]],
    {
      licenseKey: "gpl-v3",
    }
  );

  expect(engine.getCellValue({ sheet: 0, row: 0, col: 0 })).toEqual(5); // Google sheets returns 5 in this scenario, hyperformula returns 2. 
});

[warpech]

Thank you for reporting, @AJNandi. You are right that the current implementation does not work as intended with -1 as the third parameter. We will fix that.

Also, as you suggest, the docs should be improved to better explain the third parameter and the expected order of the data.

[sequba]

We should make out implementation follow the ODFF specification:

• MatchType = -1 finds the smallest value that is greater than or equal to Search in a SearchRegion where values are sorted in descending order. From a sequence of identical values ≥ Search the last value is taken. If no value ≥ Search exists, the #N/A Error is returned. If Search is of type Number and the value found is of type Text, the #N/A Error is returned.

• MatchType = 0 finds the first value that is equal to Search. Values in SearchRegion do not need to be sorted. If no value equal to Search exists, the #N/A Error is returned.

• MatchType = 1 or omitted finds the largest value that is less than or equal to Search in a SearchRegion where values are sorted in ascending order. From a sequence of identical values ≤ Search the last value is taken. If no value ≤ Search exists, the #N/A Error is returned. If Search is of type Text and the value found is of type Number, the #N/A Error is returned.

If a match is found, MATCH returns the relative position (starting from 1). For Text the comparison is case-insensitive. MatchType determines the type of search; if MatchType is 0, the SearchRegion shall be considered unsorted, and the first match is returned. If MatchType is 1, the SearchRegion may be assumed to be sorted in ascending order, with smaller Numbers before larger ones, smaller Text values before larger ones (e.g., "A" before "B", and "B" before "BA"), and FALSE before TRUE. If the types are mixed, Numbers are sorted before Text, and Text before Logicals; evaluators without a separate Logical type may include a Logical as a Number. If MatchType is -1, then SearchRegion may be assumed to be sorted in descending order (the opposite of the above). If MatchType is 1 or -1, evaluators may use binary search or other techniques so that they do not need to examine every value in linear order. MatchType defaults to 1.

Microsoft Excel seems to comply with it.

[sequba]

Hi @AJNandi

I'm more than happy to announce that we just released HyperFormula 2.2.0 where this issue is fixed. We are closing this issue as solved. If there is anything that won't work for you after updating please leave a comment.