the return of VLOOKUP is wrong

[jcuan]

This is:

- [x] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

the return of VLOOKUP is wrong when $lookupRows is 2

What is the current behavior?

wrong value

What are the steps to reproduce?

<?php

require "vendor/autoload.php";

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;

$spreadsheet = new Spreadsheet();

$spreadsheet->setActiveSheetIndex(0);
$spreadsheet->getActiveSheet()->setTitle('paper');

$workSheet = $spreadsheet->getActiveSheet();
$workSheet->setCellValue('A1', 'PaperID');
$workSheet->setCellValue('B1','AuthorID');
$workSheet->setCellValue('C1', 'Name');

//a paper
$workSheet->setCellValue('A2', '1');
$workSheet->setCellValue('B2','100');
$workSheet->setCellValue('C2', '=LOOKUP(B2,authors!$A2:$A3,authors!$B2:$B3)');

$spreadsheet->createSheet();
$spreadsheet->setActiveSheetIndex(1);
$spreadsheet->getActiveSheet()->setTitle('authors');

$workSheetTwo = $spreadsheet->getActiveSheet();
$workSheetTwo->setCellValue('A1', 'AuthorID');
$workSheetTwo->setCellValue('B1', 'Name');

//two authors
$workSheetTwo->setCellValue('A2', '100');
$workSheetTwo->setCellValue('B2', 'author_100');
$workSheetTwo->setCellValue('A3', '101');
$workSheetTwo->setCellValue('B3', 'author_101');

$spreadsheet->setActiveSheetIndex(0);
echo $spreadsheet->getActiveSheet()->getCell('C2')->getCalculatedValue().PHP_EOL;   //output is "101", the right answer is "author_100"
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('test.xlsx');

Which versions of PhpSpreadsheet and PHP are affected?

office 1.5.0
php 7.2.0

detail

LOOKUP(lookup_value, lookup_vector, result_vector)

I'm confused about why the function (PhpOffice\PhpSpreadsheet\Calculation\LookupRef::LOOKUP) treat it as a special scene when $lookupRows is equal to 2. I removed these operations and get the right value.

public static function LOOKUP($lookup_value, $lookup_vector, $result_vector = null)
    {
        $lookup_value = Functions::flattenSingleValue($lookup_value);

        if (!is_array($lookup_vector)) {
            return Functions::NA();
        }
        $lookupRows = count($lookup_vector);
        $l = array_keys($lookup_vector);
        $l = array_shift($l);
        $lookupColumns = count($lookup_vector[$l]);
        //if ((($lookupRows == 1) && ($lookupColumns > 1)) || (($lookupRows == 2) && ($lookupColumns != 2))) {
        if ((($lookupRows == 1) && ($lookupColumns > 1))) {
            $lookup_vector = self::TRANSPOSE($lookup_vector);
            $lookupRows = count($lookup_vector);
            $l = array_keys($lookup_vector);
            $lookupColumns = count($lookup_vector[array_shift($l)]);
        }

        if ($result_vector === null) {
            $result_vector = $lookup_vector;
        }
        $resultRows = count($result_vector);
        $l = array_keys($result_vector);
        $l = array_shift($l);
        $resultColumns = count($result_vector[$l]);
        //if ((($resultRows == 1) && ($resultColumns > 1)) || (($resultRows == 2) && ($resultColumns != 2))) {
        if ((($resultRows == 1) && ($resultColumns > 1))) {
            $result_vector = self::TRANSPOSE($result_vector);
            $resultRows = count($result_vector);
            $r = array_keys($result_vector);
            $resultColumns = count($result_vector[array_shift($r)]);
        }

        // if ($lookupRows == 2) {
        //     $result_vector = array_pop($lookup_vector);
        //     $lookup_vector = array_shift($lookup_vector);
        // }
        // if ($lookupColumns != 2) {
            foreach ($lookup_vector as &$value) {
                if (is_array($value)) {
                    $k = array_keys($value);
                    $key1 = $key2 = array_shift($k);
                    ++$key2;
                    $dataValue1 = $value[$key1];
                } else {
                    $key1 = 0;
                    $key2 = 1;
                    $dataValue1 = $value;
                }
                $dataValue2 = array_shift($result_vector);
                if (is_array($dataValue2)) {
                    $dataValue2 = array_shift($dataValue2);
                }
                $value = [$key1 => $dataValue1, $key2 => $dataValue2];
            }
            unset($value);
        //}

        return self::VLOOKUP($lookup_value, $lookup_vector, 2);
    }

[frantzmiccoli]

I am adding tests on this function, and will look on your specific case after.

[frantzmiccoli]

See #816