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

#N/A

#N/A

Error message

#N/A

What it means

ExcelMatch::validateLookupValue() (ExcelMatch.php:197-203) accepts only numbers, strings and booleans as MATCH's lookup_value; anything else throws #N/A, which MATCH() catches and returns as its result string. The MATCH entry point already diverts array lookup_values into the array-enabled path (line 37-39), so in practice this fires for null (blank cell reference) and objects such as RichText or DateTime.

Source

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

            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;
        }

View on GitHub (pinned to 65b080eef4)

Solutions

1. Default or skip when the key is null - MATCH has no implicit default
2. Convert RichText/DateTime cell values to scalars before matching (getCalculatedValue() or (string) cast)
3. Guard with is_numeric/is_string/is_bool before evaluation
4. Show a data-entry error when the key cell is blank instead of propagating #N/A

Example fix

// before
$result = ExcelMatch::MATCH($key, $lookupArray, 0); // '#N/A' when $key is null

// after
if ($key === null || is_object($key)) {
    throw new InvalidArgumentException('MATCH lookup value must be number, string or bool');
}
$result = ExcelMatch::MATCH($key, $lookupArray, 0);

if ($lookupValue === null || is_object($lookupValue)) {
    $lookupValue = is_object($lookupValue) ? (string) $lookupValue : null;
}
if ($lookupValue === null) {
    throw new InvalidArgumentException('MATCH lookup value is required');
}

function isMatchableValue(mixed $value): bool
{
    return is_numeric($value) || is_string($value) || is_bool($value);
}

$result = ExcelMatch::MATCH($key, $arr, 0);
if ($result === '#N/A' && !is_scalar($key)) {
    // invalid lookup value type, not merely 'not found'
}

When it happens

Trigger: =MATCH(A1, B1:B10, 0) where A1 is empty (engine passes null); ExcelMatch::MATCH(null, [...], 0); a lookup value read via getValue() from a rich-text cell (RichText object).

Common situations: Templates where the search-key cell is optional; nulls leaking from optional request parameters into direct MATCH calls; cells written with advanced/HTML value binders.

Related errors

• #VALUE!
• #NUM!
• Invalid parameter passed: formula
• Token with id $id does not exist.
• #VALUE!

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