INDEX and MATCH

Topics: Developer Forum, User Forum
Feb 25, 2009 at 10:32 AM
Hello,
I need a formula with the functions index and match. I can't find a documentation about that. Can anybody help me?

The function looks similar like that: =INDEX(D10:G68;MATCH(Q10;B10:B68;0);1)

Thank you!
Jens
Coordinator
Feb 25, 2009 at 11:08 AM
Edited Feb 25, 2009 at 11:10 AM

If that function would return the correct results when used within Excel itself, then it should work correctly within PHPExcel.

However:
";" is the Matrix row separator recognised by PHPExcel, so it doesn't recognise ";" as a parameter separator, only ","

Feb 25, 2009 at 12:13 PM
Edited Feb 25, 2009 at 12:13 PM
mmh, I use the example price_calculation.xlsx

I write:
$objReader = new PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load("price_calculation.xlsx");
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('S10','=MATCH(1199,C10:C13)');
$data = $objPHPExcel->getActiveSheet()->getCell('S10')->getCalculatedValue();

echo "<pre>Ergebnis: ";
var_dump($data);
echo "</pre>";

But the result is:
Ergebnis: string(4) "#N/A"
It is a problem that I use a german? When I open the document called the function match -> "vergleich".



Coordinator
Feb 26, 2009 at 7:28 AM

Only English names can be used for functions, so:
    =INDEX(D10:G68,MATCH(Q10,B10:B68,0),1)
should work
but
    =INDEX(D10:G68,VERGLEICH(Q10,B10:B68,0),1)

won't