{"record":{"id":"d5fbcae20291e461","repo":"PHPOffice/PhpSpreadsheet","slug":"value-d5fbca","errorCode":"#VALUE!","errorMessage":"#VALUE!","messagePattern":"#VALUE!","errorType":"exception","errorClass":"PhpOffice\\PhpSpreadsheet\\Calculation\\Exception","httpStatus":null,"severity":"error","filePath":"src/PhpSpreadsheet/Calculation/LookupRef/LookupBase.php","lineNumber":33,"sourceCode":"    }\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,\n        string $cellDataLower,\n        string $lookupLower,\n        ?int $rowNumber","sourceCodeStart":15,"sourceCodeEnd":51,"githubUrl":"https://github.com/PHPOffice/PhpSpreadsheet/blob/65b080eef4d9fd11a5796135ab145883e5c3d6a6/src/PhpSpreadsheet/Calculation/LookupRef/LookupBase.php#L15-L51","documentation":"LookupBase::validateIndexLookup() (LookupBase.php:32-34) rejects VLOOKUP/HLOOKUP index_number < 1 with #VALUE!, matching Excel: column and row indices are 1-based, so 0 and negatives are out of domain even though they are numeric.","triggerScenarios":"=VLOOKUP(x, tbl, 0, FALSE); HLOOKUP with an index computed by a 0-based PHP loop; negative index from an offset subtraction (e.g. $col - 1 where $col is 0).","commonSituations":"Developers carrying 0-based array indexing habits into spreadsheet lookups; index derived as count()-1 by mistake; blanks coerced to 0.","solutions":["Use 1-based indices - the first column of table_array is 1","Map 0-based logic explicitly: $excelIndex = $phpIndex + 1","Validate before calling: reject or clamp $indexNumber = max(1, (int) $indexNumber)","Unit-test boundary values 0, 1 and the column count"],"exampleFix":"// before\n$result = VLookup::lookup($key, $table, $phpColumnIndex); // 0-based -> '#VALUE!' at 0\n\n// after: translate the 0-based PHP index to Excel's 1-based index\n$result = VLookup::lookup($key, $table, $phpColumnIndex + 1);","handlingStrategy":"validation","validationCode":"$indexNumber = (int) $indexNumber;\nif ($indexNumber < 1) {\n    throw new InvalidArgumentException('lookup index is 1-based; got ' . $indexNumber);\n}","typeGuard":"function isOneBasedIndex(mixed $value): bool\n{\n    return is_numeric($value) && (float) $value >= 1.0;\n}","tryCatchPattern":"$result = HLookup::lookup($key, $table, $indexNumber);\nif ($result === '#VALUE!' && is_numeric($indexNumber) && (float) $indexNumber < 1.0) {\n    // 0-based or negative index slipped through\n}","preventionTips":["Translate 0-based PHP column indexes with +1 before calling","Test lookups with index values 0, 1 and count(columns)","Validate user-entered column numbers against the table's actual width"],"tags":["phpspreadsheet","php","excel","vlookup","hlookup","value-error","off-by-one"],"backgroundTag":"excel-value-error","analyzedSha":"65b080eef4d9fd11a5796135ab145883e5c3d6a6","analyzedAt":"2026-08-17T05:40:41.646Z","schemaVersion":2},"datasetVersion":"2026-08-17T09:17:11.063Z"}