PHPOffice/PhpSpreadsheet · error · PhpOffice\PhpSpreadsheet\Calculation\Exception

ExcelError::throwError($index_number)

Error message

ExcelError::throwError($index_number)

What it means

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!).

Source

Thrown at src/PhpSpreadsheet/Calculation/LookupRef/LookupBase.php:30

        if (!is_array($lookupArray)) {
            throw new Exception(ExcelError::REF());
        }
    }

    /**
     * @param mixed[] $lookupArray
     * @param float|int|string $index_number number >= 1
     */
    protected static function validateIndexLookup(array $lookupArray, $index_number): int
    {
        // index_number must be a number greater than or equal to 1.
        // Excel results are inconsistent when index is non-numeric.
        // VLOOKUP(whatever, whatever, SQRT(-1)) yields NUM error, but
        // VLOOKUP(whatever, whatever, cellref) yields REF error
        //   when cellref is '=SQRT(-1)'. So just try our best here.
        // Similar results if string (literal yields VALUE, cellRef REF).
        if (!is_numeric($index_number)) {
            throw new Exception(ExcelError::throwError($index_number));
        }
        if ($index_number < 1) {
            throw new Exception(ExcelError::VALUE());
        }

        // index_number must be less than or equal to the number of columns in lookupArray
        if (empty($lookupArray)) {
            throw new Exception(ExcelError::REF());
        }

        return (int) $index_number;
    }

    protected static function checkMatch(
        bool $bothNumeric,
        bool $bothNotNumeric,
        bool $notExactMatch,
        int $rowKey,

View on GitHub (pinned to 65b080eef4)

Solutions

1. Cast or validate the index to an int >= 1 before calling
2. Check intermediates for Excel error strings first: if (is_string($v) && str_starts_with($v, '#')) bail early
3. Pass real ints from configuration (int casts at the boundary)
4. When copying Excel formulas, remember PhpSpreadsheet resolves the literal-vs-cellref distinction by propagating whatever error string is present

Example fix

// before
$idx = $config['column']; // string "2" or "col" -> '#VALUE!'
$result = VLookup::lookup($key, $table, $idx);

// after
if (!is_numeric($idx) || (int) $idx < 1) {
    throw new InvalidArgumentException('column index must be a number >= 1');
}
$result = VLookup::lookup($key, $table, (int) $idx);

if (is_string($indexNumber) && str_starts_with($indexNumber, '#')) {
    return $indexNumber; // propagate the upstream Excel error
}
if (!is_numeric($indexNumber)) {
    throw new InvalidArgumentException('VLOOKUP/HLOOKUP index must be numeric');
}

function isNumericIndex(mixed $value): bool
{
    return is_numeric($value) && !is_string($value); // real int/float, not a numeric string
}

$result = VLookup::lookup($key, $table, $indexNumber);
if (is_string($result) && str_starts_with($result, '#') && !is_numeric($indexNumber)) {
    // index was non-numeric; $result is '#VALUE!' or the propagated error string
}

When it happens

Trigger: =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.

Common situations: 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').

Related errors

• #VALUE!
• #REF!
• #VALUE!
• #VALUE!
• #VALUE!

AI-assisted analysis of PHPOffice/PhpSpreadsheet@65b080eef4 (2026-08-17). Data as JSON: /api/errors/41c62a283e98fae1. Report an issue: GitHub.