{"record":{"id":"2846c5c4ebc8cf29","repo":"PHPOffice/PhpSpreadsheet","slug":"value-2846c5","errorCode":"#VALUE!","errorMessage":"#VALUE!","messagePattern":"#VALUE!","errorType":"exception","errorClass":"PhpOffice\\PhpSpreadsheet\\Calculation\\Exception","httpStatus":null,"severity":"error","filePath":"src/PhpSpreadsheet/Calculation/LookupRef/ExcelMatch.php","lineNumber":212,"sourceCode":"        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        }\n\n        return self::MATCHTYPE_FIRST_VALUE;\n    }\n\n    /** @param mixed[] $lookupArray */\n    private static function validateLookupArray(array $lookupArray): void\n    {\n        // Lookup_array should not be empty\n        $lookupArraySize = count($lookupArray);\n        if ($lookupArraySize <= 0) {\n            throw new Exception(ExcelError::NA());","sourceCodeStart":194,"sourceCodeEnd":230,"githubUrl":"https://github.com/PHPOffice/PhpSpreadsheet/blob/65b080eef4d9fd11a5796135ab145883e5c3d6a6/src/PhpSpreadsheet/Calculation/LookupRef/ExcelMatch.php#L194-L230","documentation":"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).","triggerScenarios":"=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.","commonSituations":"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.","solutions":["Use the correct literals: 0 = exact, 1 or omitted = largest value <= lookup, -1 = smallest value >= lookup","Coalesce blank config cells: $matchType = $cell->getValue() ?? 1","Cast bools to int and reject non-numeric strings before evaluation","Pass real ints from programmatic call sites"],"exampleFix":"// before\n$result = ExcelMatch::MATCH($key, $arr, $configCell); // blank cell -> null -> '#VALUE!'\n\n// after: normalize to a signed int with Excel's default of 1\n$matchType = is_numeric($configCell) ? (int) $configCell : 1;\n$result = ExcelMatch::MATCH($key, $arr, $matchType);","handlingStrategy":"validation","validationCode":"$matchType = is_numeric($matchType) ? (int) $matchType : 1; // Excel default: 1\n$result = ExcelMatch::MATCH($key, $arr, $matchType);","typeGuard":"function isValidMatchType(mixed $value): bool\n{\n    return is_numeric($value); // note: booleans are NOT numeric for PHP's is_numeric()\n}","tryCatchPattern":"$result = ExcelMatch::MATCH($key, $arr, $matchType);\nif ($result === '#VALUE!' && !is_numeric($matchType)) {\n    // match_type was null/string/bool\n}","preventionTips":["Use 0 for exact match, not the string 'exact'","Treat a blank match-type cell as 1 (Excel's default)","Cast bool flags to int before passing them as match_type"],"tags":["phpspreadsheet","php","excel","match","lookup","value-error","argument-validation"],"backgroundTag":"excel-value-error","analyzedSha":"65b080eef4d9fd11a5796135ab145883e5c3d6a6","analyzedAt":"2026-08-17T05:40:41.646Z","schemaVersion":2},"datasetVersion":"2026-08-17T09:17:11.063Z"}