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

#VALUE!

#VALUE!

Error message

#VALUE!

What it means

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.

Source

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

    }

    /**
     * @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,
        string $cellDataLower,
        string $lookupLower,
        ?int $rowNumber

View on GitHub (pinned to 65b080eef4)

Solutions

1. Use 1-based indices - the first column of table_array is 1
2. Map 0-based logic explicitly: $excelIndex = $phpIndex + 1
3. Validate before calling: reject or clamp $indexNumber = max(1, (int) $indexNumber)
4. Unit-test boundary values 0, 1 and the column count

Example fix

// before
$result = VLookup::lookup($key, $table, $phpColumnIndex); // 0-based -> '#VALUE!' at 0

// after: translate the 0-based PHP index to Excel's 1-based index
$result = VLookup::lookup($key, $table, $phpColumnIndex + 1);

$indexNumber = (int) $indexNumber;
if ($indexNumber < 1) {
    throw new InvalidArgumentException('lookup index is 1-based; got ' . $indexNumber);
}

function isOneBasedIndex(mixed $value): bool
{
    return is_numeric($value) && (float) $value >= 1.0;
}

$result = HLookup::lookup($key, $table, $indexNumber);
if ($result === '#VALUE!' && is_numeric($indexNumber) && (float) $indexNumber < 1.0) {
    // 0-based or negative index slipped through
}

When it happens

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

Common situations: Developers carrying 0-based array indexing habits into spreadsheet lookups; index derived as count()-1 by mistake; blanks coerced to 0.

Related errors

• ExcelError::throwError($index_number)
• #REF!
• Token with id $id does not exist.
• #VALUE!
• #VALUE!

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