MATCH function

[rolandsusans]

This is:

• a bug report

In some case currently implemented MATCH function produces a different result from original MS Excel implementation. See red ones in these screenshots:

The file can be found here.

The bug is related to:

• not correctly handling boolean values ( conversion to string should be done only when the value is a string )
• match_type = -1, error when firs element is different data type and < value being searched
• match_type = -1 when at least one element in lookup array found, but the next element is less than the searched value ( unordered list ) is being checked till the end.
• not supporting *?~ Excel functionality

How MATCH function should work:

MS Excel docs here.

Match type
-1	MATCH finds the smallest value that is greater than or equal tolookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.
0	MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.
1	MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

MATCH does not distinguish between uppercase and lowercase letters when matching text values.

If match_type is 0 and lookup_value is a text string, you can use the wildcard characters — the question mark (?) and asterisk (*) — in the lookup_value argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

What is the expected behaviour?

Expected behaviour listed in E column. ( calculated by opening the worksheet in Excel for MAC 16.26 )

What is the current behaviour?

Current behaviour listed in G column. Calculated using PHP script using the library. ( Calculate E and store the result into G ).

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

require __DIR__ . '/vendor/autoload.php';

var_dump(\PhpOffice\PhpSpreadsheet\Calculation\LookupRef::MATCH(TRUE, [TRUE, FALSE], 0));//#N/A intedad of 1
 var_dump(\PhpOffice\PhpSpreadsheet\Calculation\LookupRef::MATCH('a*b', ['acb'], 0));//#N/A instedad of 1

Which versions of PhpSpreadsheet and PHP are affected?

• "phpoffice/phpspreadsheet": "^1.6",
• all versions of PHP

[rolandsusans]

I will try to fix this one.