{"record":{"id":"e3f10f75b45d915e","repo":"PHPOffice/PhpSpreadsheet","slug":"n-a","errorCode":"#N/A","errorMessage":"#N/A","messagePattern":"#N/A","errorType":"exception","errorClass":"PhpOffice\\PhpSpreadsheet\\Calculation\\Exception","httpStatus":null,"severity":"error","filePath":"src/PhpSpreadsheet/Calculation/LookupRef/ExcelMatch.php","lineNumber":201,"sourceCode":"            if ($bothNumeric && $lookupValue == $lookupArrayValue) {\n                return $i; // exact match, as above\n            }\n            if (($typeMatch || $bothNumeric) && $lookupArrayValue >= $lookupValue) {\n                $valueKey = $i;\n            } elseif ($typeMatch && $lookupArrayValue < $lookupValue) {\n                //Excel algorithm gives up immediately if the first element is smaller than the searched value\n                break;\n            }\n        }\n\n        return $valueKey;\n    }\n\n    private static function validateLookupValue(mixed $lookupValue): void\n    {\n        // Lookup_value type has to be number, text, or logical values\n        if ((!is_numeric($lookupValue)) && (!is_string($lookupValue)) && (!is_bool($lookupValue))) {\n            throw new Exception(ExcelError::NA());\n        }\n    }\n\n    private static function validateMatchType(mixed $matchType): int\n    {\n        // Match_type is 0, 1 or -1\n        // However Excel accepts other numeric values,\n        //  including numeric strings and floats.\n        //  It seems to just be interested in the sign.\n        if (!is_numeric($matchType)) {\n            throw new Exception(ExcelError::Value());\n        }\n        if ($matchType > 0) {\n            return self::MATCHTYPE_LARGEST_VALUE;\n        }\n        if ($matchType < 0) {\n            return self::MATCHTYPE_SMALLEST_VALUE;\n        }","sourceCodeStart":183,"sourceCodeEnd":219,"githubUrl":"https://github.com/PHPOffice/PhpSpreadsheet/blob/65b080eef4d9fd11a5796135ab145883e5c3d6a6/src/PhpSpreadsheet/Calculation/LookupRef/ExcelMatch.php#L183-L219","documentation":"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.","triggerScenarios":"=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).","commonSituations":"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.","solutions":["Default or skip when the key is null - MATCH has no implicit default","Convert RichText/DateTime cell values to scalars before matching (getCalculatedValue() or (string) cast)","Guard with is_numeric/is_string/is_bool before evaluation","Show a data-entry error when the key cell is blank instead of propagating #N/A"],"exampleFix":"// before\n$result = ExcelMatch::MATCH($key, $lookupArray, 0); // '#N/A' when $key is null\n\n// after\nif ($key === null || is_object($key)) {\n    throw new InvalidArgumentException('MATCH lookup value must be number, string or bool');\n}\n$result = ExcelMatch::MATCH($key, $lookupArray, 0);","handlingStrategy":"validation","validationCode":"if ($lookupValue === null || is_object($lookupValue)) {\n    $lookupValue = is_object($lookupValue) ? (string) $lookupValue : null;\n}\nif ($lookupValue === null) {\n    throw new InvalidArgumentException('MATCH lookup value is required');\n}","typeGuard":"function isMatchableValue(mixed $value): bool\n{\n    return is_numeric($value) || is_string($value) || is_bool($value);\n}","tryCatchPattern":"$result = ExcelMatch::MATCH($key, $arr, 0);\nif ($result === '#N/A' && !is_scalar($key)) {\n    // invalid lookup value type, not merely 'not found'\n}","preventionTips":["Read the key via getCalculatedValue() to avoid RichText objects","Default blank key cells before evaluation","Remember arrays are legal (array-enabled path) but null/objects are not"],"tags":["phpspreadsheet","php","excel","match","lookup","na-error","argument-validation"],"backgroundTag":"excel-na-error","analyzedSha":"65b080eef4d9fd11a5796135ab145883e5c3d6a6","analyzedAt":"2026-08-17T05:40:41.646Z","schemaVersion":2},"datasetVersion":"2026-08-17T09:17:11.063Z"}