This project has moved. For the latest updates, please go here.

VLOOKUP fails due to locale issues


Excel (at least in its german version) seems to sort german umlauts in a different manner than PHPs uasort(). This can cause big trouble when using VLOOKUP with $not_exact_match == true.

AFAIKS, a quick bugfix would be to use uasort() in LookupRef.php, line 653 in any case, regardless of the value of $not_exact_match (besides, the following code does not seem to efficient to me; perhaps some binary search could speed up VLOOKUP references quite a lot).

I have a small test file to reproduce the problem, however the upload does not work right now... I'll try again later on.

file attachments


SlowFox wrote Oct 11, 2012 at 10:47 AM

The following script can reproduce the problem in connection with the attached excel file. In Excel B1, B2 and B3 all show "ST", but PHPExcel sorts different ("Stück" after "Stunde") and thus evaluates B2 and B3 to "HUR".


require_once 'phpexcel/Classes/PHPExcel.php';

$type = PHPExcel_IOFactory::identify('test.xlsx');
$reader = PHPExcel_IOFactory::createReader($type);
$handle = $reader->load('test.xlsx');
$sheet = $handle->getSheet();

$rowIterator = new PHPExcel_Worksheet_RowIterator($sheet);

foreach ($rowIterator as $row) {
    $cellIterator = $row->getCellIterator();
    foreach ($cellIterator as $cell) {
            echo "\n";


wrote Feb 22, 2013 at 2:01 AM