PHPOffice/PhpSpreadsheet · error · PhpOffice\PhpSpreadsheet\Calculation\Exception

#VALUE!

#VALUE!

Error message

#VALUE!

What it means

ExcelMatch::validateMatchType() (ExcelMatch.php:205-222) requires MATCH's match_type to be numeric and throws #VALUE! otherwise. Numeric strings ('-1', '0.5') and floats are accepted - only the sign is used - but null, non-numeric strings, arrays and even PHP booleans are rejected (is_numeric(true) is false in PHP, so TRUE as match_type also errors, unlike numeric strings).

Source

Thrown at src/PhpSpreadsheet/Calculation/LookupRef/ExcelMatch.php:212

        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());

View on GitHub (pinned to 65b080eef4)

Solutions

1. Use the correct literals: 0 = exact, 1 or omitted = largest value <= lookup, -1 = smallest value >= lookup
2. Coalesce blank config cells: $matchType = $cell->getValue() ?? 1
3. Cast bools to int and reject non-numeric strings before evaluation
4. Pass real ints from programmatic call sites

Example fix

// before
$result = ExcelMatch::MATCH($key, $arr, $configCell); // blank cell -> null -> '#VALUE!'

// after: normalize to a signed int with Excel's default of 1
$matchType = is_numeric($configCell) ? (int) $configCell : 1;
$result = ExcelMatch::MATCH($key, $arr, $matchType);

$matchType = is_numeric($matchType) ? (int) $matchType : 1; // Excel default: 1
$result = ExcelMatch::MATCH($key, $arr, $matchType);

function isValidMatchType(mixed $value): bool
{
    return is_numeric($value); // note: booleans are NOT numeric for PHP's is_numeric()
}

$result = ExcelMatch::MATCH($key, $arr, $matchType);
if ($result === '#VALUE!' && !is_numeric($matchType)) {
    // match_type was null/string/bool
}

When it happens

Trigger: =MATCH(x, rng, C1) where C1 is blank (null); MATCH(x, rng, "exact") - a common but invalid idiom (exact match is 0); passing TRUE instead of 1; a string flag from JSON/config used uncast.

Common situations: Match type driven by a config cell users leave empty; formulas copied from tutorials using text placeholders; PHP code passing a bool where Excel semantics expect 1/0/-1.

Related errors

• #N/A
• #NUM!
• #VALUE!
• ExcelError::throwError($index_number)
• #VALUE!

AI-assisted analysis of PHPOffice/PhpSpreadsheet@65b080eef4 (2026-08-17). Data as JSON: /api/errors/2846c5c4ebc8cf29. Report an issue: GitHub.