{"record":{"id":"41c62a283e98fae1","repo":"PHPOffice/PhpSpreadsheet","slug":"excelerror-throwerror-index-number","errorCode":null,"errorMessage":"ExcelError::throwError($index_number)","messagePattern":"ExcelError::throwError\\(\\$index_number\\)","errorType":"exception","errorClass":"PhpOffice\\PhpSpreadsheet\\Calculation\\Exception","httpStatus":null,"severity":"error","filePath":"src/PhpSpreadsheet/Calculation/LookupRef/LookupBase.php","lineNumber":30,"sourceCode":"        if (!is_array($lookupArray)) {\n            throw new Exception(ExcelError::REF());\n        }\n    }\n\n    /**\n     * @param mixed[] $lookupArray\n     * @param float|int|string $index_number number >= 1\n     */\n    protected static function validateIndexLookup(array $lookupArray, $index_number): int\n    {\n        // index_number must be a number greater than or equal to 1.\n        // Excel results are inconsistent when index is non-numeric.\n        // VLOOKUP(whatever, whatever, SQRT(-1)) yields NUM error, but\n        // VLOOKUP(whatever, whatever, cellref) yields REF error\n        //   when cellref is '=SQRT(-1)'. So just try our best here.\n        // Similar results if string (literal yields VALUE, cellRef REF).\n        if (!is_numeric($index_number)) {\n            throw new Exception(ExcelError::throwError($index_number));\n        }\n        if ($index_number < 1) {\n            throw new Exception(ExcelError::VALUE());\n        }\n\n        // index_number must be less than or equal to the number of columns in lookupArray\n        if (empty($lookupArray)) {\n            throw new Exception(ExcelError::REF());\n        }\n\n        return (int) $index_number;\n    }\n\n    protected static function checkMatch(\n        bool $bothNumeric,\n        bool $bothNotNumeric,\n        bool $notExactMatch,\n        int $rowKey,","sourceCodeStart":12,"sourceCodeEnd":48,"githubUrl":"https://github.com/PHPOffice/PhpSpreadsheet/blob/65b080eef4d9fd11a5796135ab145883e5c3d6a6/src/PhpSpreadsheet/Calculation/LookupRef/LookupBase.php#L12-L48","documentation":"LookupBase::validateIndexLookup() (LookupBase.php:21-42) rejects a non-numeric VLOOKUP/HLOOKUP index_number by throwing with ExcelError::throwError($index_number): if the input is already an Excel error string ('#DIV/0!', '#N/A', ...) that exact error is propagated; any other string or value becomes '#VALUE!'. The source comment documents why Excel itself is inconsistent here (literal SQRT(-1) gives #NUM!, a cell reference to it gives #REF!).","triggerScenarios":"=VLOOKUP(x, tbl, \"col\") -> '#VALUE!'; VLOOKUP(x, tbl, A2) where A2 evaluates to '#DIV/0!' -> '#DIV/0!'; an index computed by a sub-calculation that errored.","commonSituations":"Column index built by a formula that can fail; hardcoded string indices from config ('\"2\"'); localized number formats making numeric-looking strings non-numeric (e.g. '2,0').","solutions":["Cast or validate the index to an int >= 1 before calling","Check intermediates for Excel error strings first: if (is_string($v) && str_starts_with($v, '#')) bail early","Pass real ints from configuration (int casts at the boundary)","When copying Excel formulas, remember PhpSpreadsheet resolves the literal-vs-cellref distinction by propagating whatever error string is present"],"exampleFix":"// before\n$idx = $config['column']; // string \"2\" or \"col\" -> '#VALUE!'\n$result = VLookup::lookup($key, $table, $idx);\n\n// after\nif (!is_numeric($idx) || (int) $idx < 1) {\n    throw new InvalidArgumentException('column index must be a number >= 1');\n}\n$result = VLookup::lookup($key, $table, (int) $idx);","handlingStrategy":"validation","validationCode":"if (is_string($indexNumber) && str_starts_with($indexNumber, '#')) {\n    return $indexNumber; // propagate the upstream Excel error\n}\nif (!is_numeric($indexNumber)) {\n    throw new InvalidArgumentException('VLOOKUP/HLOOKUP index must be numeric');\n}","typeGuard":"function isNumericIndex(mixed $value): bool\n{\n    return is_numeric($value) && !is_string($value); // real int/float, not a numeric string\n}","tryCatchPattern":"$result = VLookup::lookup($key, $table, $indexNumber);\nif (is_string($result) && str_starts_with($result, '#') && !is_numeric($indexNumber)) {\n    // index was non-numeric; $result is '#VALUE!' or the propagated error string\n}","preventionTips":["Cast config-provided indices to int at the boundary","Check intermediate cells for '#' error strings before using them as indices","Keep lookup indices as PHP ints in application code"],"tags":["phpspreadsheet","php","excel","vlookup","hlookup","value-error","error-propagation"],"backgroundTag":"excel-value-error","analyzedSha":"65b080eef4d9fd11a5796135ab145883e5c3d6a6","analyzedAt":"2026-08-17T05:40:41.646Z","schemaVersion":2},"datasetVersion":"2026-08-17T09:17:11.063Z"}