Problem with VLOOKUP

[duccoder]

I have code:

$workSheet->setCellValue('A1', 'M20');
$workSheet->setCellValue('A2', 'M24');
$workSheet->setCellValue('A3', 'M30');
$workSheet->setCellValue('A4', 'M36');
$workSheet->setCellValue('B1', 'a1');
$workSheet->setCellValue('B2', 'b1');
$workSheet->setCellValue('B3', 'c1');
$workSheet->setCellValue('B4', 'd1');
$workSheet->setCellValue('C1', 'a2');
$workSheet->setCellValue('C2', 'b2');
$workSheet->setCellValue('C3', 'c2');
$workSheet->setCellValue('C4', 'd2');
$workSheet->setCellValue('G6', '=VLOOKUP("M20",A1:C4,3)');
echo $workSheet->getCell('G6')->getCalculatedValue();

So it return a2 ==> TRUE But when I change code:

$workSheet->setCellValue('A1', 'M20');
$workSheet->setCellValue('A2', 'M24');
$workSheet->setCellValue('A3', 'M30');
$workSheet->setCellValue('A4', 'M36');
$workSheet->setCellValue('B1', '230');
$workSheet->setCellValue('B2', '231');
$workSheet->setCellValue('B3', '232');
$workSheet->setCellValue('B4', '233');
$workSheet->setCellValue('C1', '200');
$workSheet->setCellValue('C2', '300');
$workSheet->setCellValue('C3', '400');
$workSheet->setCellValue('C4', '500');
$workSheet->setCellValue('G6', '=VLOOKUP("M20",A1:C4,3)');
echo $workSheet->getCell('G6')->getCalculatedValue();

I get #N/A instead 200. VLOOKUP return TRUE value if table_array is string? Help me

[duccoder]

I think bug is in method static VLOOKUP line 739 to 742 in LookupRef.php

if ((is_numeric($lookup_value) && is_numeric($result)) ||
    (!is_numeric($lookup_value) && !is_numeric($result))) {
    return $result;
}

So with my example above, $lookup_value = "M20" => $result exactly is string(20) So if $result is number so $lookup_value is number?

[tinchoz49]

same error!!

[StorytellerCZ]

The logic here seems to be flawed to me. In my case I have a string lookup value which will return a numeric value, but it will not make it through the test that @fabienzet pointed out. So in my case I just want to return the $result that the function has already found. Replacing the entire logic there with just return $lookup_array[$rowNumber][$returnColumn]; worked for me, but I'm not sure that is the solution for more complex things.

[duccoder]

@AdwinTrave I hard code while I 'm waiting for a solution of my problem.

//if ((is_numeric($lookup_value) && is_numeric($result)) ||
//    (!is_numeric($lookup_value) && !is_numeric($result))) {
    return $result;
//}

and this return result I need. You can see HLOOKUP function.

[StorytellerCZ]

@fabienzet Same thing I did :-)