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

#REF!

#REF!

Error message

#REF!

What it means

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.

Source

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

<?php

namespace PhpOffice\PhpSpreadsheet\Calculation\LookupRef;

use PhpOffice\PhpSpreadsheet\Calculation\Exception;
use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;

abstract class LookupBase
{
    protected static function validateLookupArray(mixed $lookupArray): void
    {
        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));
        }

View on GitHub (pinned to 65b080eef4)

Solutions

1. Wrap scalars: $table = is_array($table) ? $table : [[$table]]
2. Use a real multi-cell range for table_array
3. Type-check $lookupArray before calling VLookup/HLookup/XLookup
4. Re-check data shape after any flatten/extract step

Example fix

// before
$result = VLookup::lookup($key, $maybeScalar, 2); // '#REF!' when $maybeScalar is 5

// after
$table = is_array($table) ? $table : [[$table]];
$result = VLookup::lookup($key, $table, 2);

$table = is_array($table) ? $table : [[$table]];
$result = VLookup::lookup($key, $table, $indexNumber);

function isLookupTable(mixed $value): bool
{
    return is_array($value);
}

$result = VLookup::lookup($key, $table, 2);
if ($result === '#REF!' && !is_array($table)) {
    // table_array collapsed to a scalar - rebuild it as [[...]]
}

When it happens

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

Common situations: Calling the lookup classes directly with unvalidated data; single-cell table ranges; union-typed variables collapsing to scalar after flattenSingleValue().

Related errors

• ExcelError::throwError($index_number)
• #VALUE!
• #VALUE!
• #REF!
• #VALUE!

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