{"record":{"id":"b16a488670780497","repo":"PHPOffice/PhpSpreadsheet","slug":"ref-b16a48","errorCode":"#REF!","errorMessage":"#REF!","messagePattern":"#REF!","errorType":"exception","errorClass":"PhpOffice\\PhpSpreadsheet\\Calculation\\Exception","httpStatus":null,"severity":"error","filePath":"src/PhpSpreadsheet/Calculation/LookupRef/LookupBase.php","lineNumber":13,"sourceCode":"<?php\n\nnamespace PhpOffice\\PhpSpreadsheet\\Calculation\\LookupRef;\n\nuse PhpOffice\\PhpSpreadsheet\\Calculation\\Exception;\nuse PhpOffice\\PhpSpreadsheet\\Calculation\\Information\\ExcelError;\n\nabstract class LookupBase\n{\n    protected static function validateLookupArray(mixed $lookupArray): void\n    {\n        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        }","sourceCodeStart":1,"sourceCodeEnd":31,"githubUrl":"https://github.com/PHPOffice/PhpSpreadsheet/blob/65b080eef4d9fd11a5796135ab145883e5c3d6a6/src/PhpSpreadsheet/Calculation/LookupRef/LookupBase.php#L1-L31","documentation":"LookupBase::validateLookupArray() (LookupBase.php:10-15) requires the table_array argument of VLOOKUP (VLookup::lookup), HLOOKUP (HLookup::lookup) and XLookup to be a PHP array; a scalar throws #REF!. The calculation engine normally passes cell ranges as arrays, so this mostly bites direct PHP calls or degenerate single-cell references that flatten to a scalar.","triggerScenarios":"VLookup::lookup($value, 5, 2); =VLOOKUP(x, A1, 2) where the single-cell table reference resolves to a scalar; passing a variable that is sometimes a scalar (one flattened row).","commonSituations":"Calling the lookup classes directly with unvalidated data; single-cell table ranges; union-typed variables collapsing to scalar after flattenSingleValue().","solutions":["Wrap scalars: $table = is_array($table) ? $table : [[$table]]","Use a real multi-cell range for table_array","Type-check $lookupArray before calling VLookup/HLookup/XLookup","Re-check data shape after any flatten/extract step"],"exampleFix":"// before\n$result = VLookup::lookup($key, $maybeScalar, 2); // '#REF!' when $maybeScalar is 5\n\n// after\n$table = is_array($table) ? $table : [[$table]];\n$result = VLookup::lookup($key, $table, 2);","handlingStrategy":"type-guard","validationCode":"$table = is_array($table) ? $table : [[$table]];\n$result = VLookup::lookup($key, $table, $indexNumber);","typeGuard":"function isLookupTable(mixed $value): bool\n{\n    return is_array($value);\n}","tryCatchPattern":"$result = VLookup::lookup($key, $table, 2);\nif ($result === '#REF!' && !is_array($table)) {\n    // table_array collapsed to a scalar - rebuild it as [[...]]\n}","preventionTips":["Check is_array() on any programmatically built table_array","Use multi-cell ranges, not single cells, for lookup tables","Re-validate shape after flattenSingleValue()/extract steps"],"tags":["phpspreadsheet","php","excel","vlookup","hlookup","xlookup","ref-error","type-mismatch"],"backgroundTag":"excel-ref-error","analyzedSha":"65b080eef4d9fd11a5796135ab145883e5c3d6a6","analyzedAt":"2026-08-17T05:40:41.646Z","schemaVersion":2},"datasetVersion":"2026-08-17T09:17:11.063Z"}