|float|int|string The relative position of the found item */ public static function MATCH(mixed $lookupValue, mixed $lookupArray, mixed $matchType = self::MATCHTYPE_LARGEST_VALUE): array|string|int|float { if (is_array($lookupValue)) { return self::evaluateArrayArgumentsIgnore([self::class, __FUNCTION__], 1, $lookupValue, $lookupArray, $matchType); } $lookupArray = Functions::flattenArray($lookupArray); try { // Input validation self::validateLookupValue($lookupValue); $matchType = self::validateMatchType($matchType); self::validateLookupArray($lookupArray); $keySet = array_keys($lookupArray); if ($matchType == self::MATCHTYPE_LARGEST_VALUE) { // If match_type is 1 the list has to be processed from last to first $lookupArray = array_reverse($lookupArray); $keySet = array_reverse($keySet); } $lookupArray = self::prepareLookupArray($lookupArray, $matchType); } catch (Exception $e) { return $e->getMessage(); } // MATCH() is not case sensitive, so we convert lookup value to be lower cased if it's a string type. if (is_string($lookupValue)) { $lookupValue = StringHelper::strToLower($lookupValue); } $valueKey = match ($matchType) { self::MATCHTYPE_LARGEST_VALUE => self::matchLargestValue($lookupArray, $lookupValue, $keySet), self::MATCHTYPE_FIRST_VALUE => self::matchFirstValue($lookupArray, $lookupValue), default => self::matchSmallestValue($lookupArray, $lookupValue), }; if ($valueKey !== null) { return ++$valueKey; //* @phpstan-ignore-line } // Unsuccessful in finding a match, return #N/A error value return ExcelError::NA(); } /** @param mixed[] $lookupArray */ private static function matchFirstValue(array $lookupArray, mixed $lookupValue): int|string|null { if (is_string($lookupValue)) { $valueIsString = true; $wildcard = WildcardMatch::wildcard($lookupValue); } else { $valueIsString = false; $wildcard = ''; } $valueIsNumeric = is_int($lookupValue) || is_float($lookupValue); foreach ($lookupArray as $i => $lookupArrayValue) { if ( $valueIsString && is_string($lookupArrayValue) ) { if (WildcardMatch::compare($lookupArrayValue, $wildcard)) { return $i; // wildcard match } } else { if ($lookupArrayValue === $lookupValue) { return $i; // exact match } if ( $valueIsNumeric && (is_float($lookupArrayValue) || is_int($lookupArrayValue)) && $lookupArrayValue == $lookupValue ) { return $i; // exact match } } } return null; } /** * @param mixed[] $lookupArray * @param mixed[] $keySet */ private static function matchLargestValue(array $lookupArray, mixed $lookupValue, array $keySet): mixed { if (is_string($lookupValue)) { if (Functions::getCompatibilityMode() === Functions::COMPATIBILITY_OPENOFFICE) { $wildcard = WildcardMatch::wildcard($lookupValue); foreach (array_reverse($lookupArray) as $i => $lookupArrayValue) { if (is_string($lookupArrayValue) && WildcardMatch::compare($lookupArrayValue, $wildcard)) { return $i; } } } else { foreach ($lookupArray as $i => $lookupArrayValue) { if ($lookupArrayValue === $lookupValue) { return $keySet[$i]; } } } } $valueIsNumeric = is_int($lookupValue) || is_float($lookupValue); foreach ($lookupArray as $i => $lookupArrayValue) { if ($valueIsNumeric && (is_int($lookupArrayValue) || is_float($lookupArrayValue))) { if ($lookupArrayValue <= $lookupValue) { return array_search($i, $keySet); } } $typeMatch = gettype($lookupValue) === gettype($lookupArrayValue); if ($typeMatch && ($lookupArrayValue <= $lookupValue)) { return array_search($i, $keySet); } } return null; } /** @param mixed[] $lookupArray */ private static function matchSmallestValue(array $lookupArray, mixed $lookupValue): int|string|null { $valueKey = null; if (is_string($lookupValue)) { if (Functions::getCompatibilityMode() === Functions::COMPATIBILITY_OPENOFFICE) { $wildcard = WildcardMatch::wildcard($lookupValue); foreach ($lookupArray as $i => $lookupArrayValue) { if (is_string($lookupArrayValue) && WildcardMatch::compare($lookupArrayValue, $wildcard)) { return $i; } } } } $valueIsNumeric = is_int($lookupValue) || is_float($lookupValue); // The basic algorithm is: // Iterate and keep the highest match until the next element is smaller than the searched value. // Return immediately if perfect match is found foreach ($lookupArray as $i => $lookupArrayValue) { $typeMatch = gettype($lookupValue) === gettype($lookupArrayValue); $bothNumeric = $valueIsNumeric && (is_int($lookupArrayValue) || is_float($lookupArrayValue)); if ($lookupArrayValue === $lookupValue) { // Another "special" case. If a perfect match is found, // the algorithm gives up immediately return $i; } if ($bothNumeric && $lookupValue == $lookupArrayValue) { return $i; // exact match, as above } if (($typeMatch || $bothNumeric) && $lookupArrayValue >= $lookupValue) { $valueKey = $i; } elseif ($typeMatch && $lookupArrayValue < $lookupValue) { //Excel algorithm gives up immediately if the first element is smaller than the searched value break; } } return $valueKey; } private static function validateLookupValue(mixed $lookupValue): void { // Lookup_value type has to be number, text, or logical values if ((!is_numeric($lookupValue)) && (!is_string($lookupValue)) && (!is_bool($lookupValue))) { throw new Exception(ExcelError::NA()); } } private static function validateMatchType(mixed $matchType): int { // Match_type is 0, 1 or -1 // However Excel accepts other numeric values, // including numeric strings and floats. // It seems to just be interested in the sign. if (!is_numeric($matchType)) { throw new Exception(ExcelError::Value()); } if ($matchType > 0) { return self::MATCHTYPE_LARGEST_VALUE; } if ($matchType < 0) { return self::MATCHTYPE_SMALLEST_VALUE; } return self::MATCHTYPE_FIRST_VALUE; } /** @param mixed[] $lookupArray */ private static function validateLookupArray(array $lookupArray): void { // Lookup_array should not be empty $lookupArraySize = count($lookupArray); if ($lookupArraySize <= 0) { throw new Exception(ExcelError::NA()); } } /** * @param mixed[] $lookupArray * * @return mixed[] */ private static function prepareLookupArray(array $lookupArray, mixed $matchType): array { // Lookup_array should contain only number, text, or logical values, or empty (null) cells foreach ($lookupArray as $i => $value) { // check the type of the value if ((!is_numeric($value)) && (!is_string($value)) && (!is_bool($value)) && ($value !== null)) { throw new Exception(ExcelError::NA()); } // Convert strings to lowercase for case-insensitive testing if (is_string($value)) { $lookupArray[$i] = StringHelper::strToLower($value); } if ( ($value === null) && (($matchType == self::MATCHTYPE_LARGEST_VALUE) || ($matchType == self::MATCHTYPE_SMALLEST_VALUE)) ) { unset($lookupArray[$i]); } } return $lookupArray; } }