Excel function index always returns #value!

[christian-06]

System: Nginx with PHP7.0.14, PHPExcel V1.8
Excel-Template:

Result in PHPExcel for A10 is "#VALUE!"
What I'm doing wrong?
Correct would be "AT3456"

[PowerKiKi]

Please provide a Minimal, Complete, and Verifiable example of code that exhibits this issue (without nginx, and creating the spreadsheet by code without reading from files). That way there will be more chance for the issue to be solved.

[christian-06]

<?php
error_reporting(E_ALL);
ini_set('display_errors', true);
require_once 'PHPExcel-1.8/Classes/PHPExcel.php';

$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'Title 1')->setCellValue('B1', 'AT1234')
            ->setCellValue('A2', 'Title 2')->setCellValue('B2', 'AT3456')
            ->setCellValue('A3', 'Title 3')->setCellValue('B3', 'AT5678')
            ->setCellValue('A4', 'Title 4')->setCellValue('B4', 'AT8875')
            ->setCellValue('A5', 'Title 5')->setCellValue('B5', 'AT5457')
            ->setCellValue('A7', '2')      ->setCellValue('B7', '=INDEX(A1:B5,A7,2)');
            // In Excel, parameters must be per ; separated here by ,

var_dump($objPHPExcel->getActiveSheet()->getCell('B7')->getValue()); # Return String '=INDEX(A1:C5,A7,2)'
var_dump($objPHPExcel->getActiveSheet()->getCell('B7')->getCalculatedValue()); # Return String '#VALUE!'

Example without xlsx file purely created in code, comes to the same error.
What am I doing wrong?
I think the error in file "PHPExcel / Calculation / LookupRef.php" found in line 635?
An array is compare to an integer value
Array > int

[PowerKiKi]

The example can be even reduced further down to the following. And I am able to reproduce and confirm the bug. What you said about array compared to int seems to be correct. And it seems it is because the $rowNum argument is wrong. The INDEX() method receives an array, but is actually expecting an int. If we change that argument to an int, it works as expected. So that means the method INDEX() is correct and the bug is somewhere before that method...

I'll let you dig further, but I suspect it may be something in PHPExcel_Calculation::_processTokenStack(). Also I suggest you keep debugging this after upgrading to PhpSpreadsheet, because PHPExcel is not developed anymore, and PhpSpreadsheet provides a better environnement for submitting patches (unit testing, code style checking, etc.)

<?php

require 'Classes/PHPExcel.php';

$arrayValues = array(
    1 => array(
        'A' => 'Title 1',
        'B' => 'AT1234',
    ),
    2 => array(
        'A' => 'Title 2',
        'B' => 'AT3456',
    ),
    3 => array(
        'A' => 'Title 3',
        'B' => 'AT5678',
    ),
    4 => array(
        'A' => 'Title 4',
        'B' => 'AT8875',
    ),
    5 => array(
        'A' => 'Title 5',
        'B' => 'AT5457',
    ),
);

$rowNum = array(
    7 => array(
        'A' => 2.0,
    ),
);

$columnNum = 2;

var_dump(PHPExcel_Calculation_LookupRef::INDEX($arrayValues, $rowNum, $columnNum)); // Wrong
var_dump(PHPExcel_Calculation_LookupRef::INDEX($arrayValues, 2, $columnNum)); // Correct