[SOLVED] Formula Returning 0

Topics: Developer Forum
Jul 25, 2008 at 11:12 PM

I have an XLSX document with 2 worksheets. A cell on the first sheet has a formula which references cells in both sheets, some of which themselves are formulas.

Calling this:
<?php echo $excel->getActiveSheet()->getCell('I7')->getValue(); ?>

Returns the following:
=ROUND(VLOOKUP($E10,'Sheet 2'!$C$3:$J$44,MATCH(I$5,'Sheet 2'!$C$3:$J$3,0),FALSE),0)

Calling this:
<?php echo $excel->getActiveSheet()->getCell('I7')->getCalculatedValue(); ?>

Always returns 0.

Is there something in that formula that is not compatible with PHPExcel or am I just doing it wrong?

Coordinator
Jul 26, 2008 at 12:16 PM
While over 200 of Excel's functions have now been implemented in PHPExcel (including MATCH, although this only exists in the latest CVS version of the code, rather than the 1.6.2 Release version), VLOOKUP hasn't yet been implemented. VLOOKUP and HLOOKUP are on the "To do list", but not yet available.
Jul 27, 2008 at 8:22 PM
Edited Jul 27, 2008 at 8:31 PM
Mark,

Thanks for your quick response. I downloaded Friday's commit and started the VLOOKUP implementation. Thus far, it does not have much error checking and only works on exact matches for the lookup value. I will clean this up more in the next few days and post an update here. If there's a painless way to commit this into the repository, I'm happy to do so. Otherwise, I'll just pass the code to you and let you handle since I'm just working on this far enough to make it suit my purposes for my current project.

Here's what I have thus far:

/**
* VLOOKUP
* The VLOOKUP function searches for value in the left-most column of lookup_array and returns the value in the same row based on the index_number.
* @param lookup_value The value that you want to match in lookup_array
* @param lookup_array The range of cells being searched
* @param index_number The column number in table_array from which the matching value must be returned. The first column is 1.
* @param not_exact_match Determines if you are looking for an exact match based on lookup_value.
* @return mixed The value of the found cell
*/
public static function VLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match=true) {
// index_number must be greater than or equal to 1
if ($index_number < 1) {
return self::$_errorCodes['value'];
}
// index_number must be less than or equal to the number of columns in lookup_array
if ($index_number > count($lookup_array)) {
return self::$_errorCodes['reference'];
}
// re-index lookup_array with numeric keys starting at 1
array_unshift($lookup_array, array());
$lookup_array = array_slice(array_values($lookup_array), 1, count($lookup_array), true);
$row_number = array_search($lookup_value, $lookup_array[1]);
return $lookup_array[$index_number][$row_number];
/*
var_dump($lookup_value);
var_dump($lookup_array);
var_dump($index_number);
var_dump($not_exact_match);
exit();
*/
}

Sources: http://www.techonthenet.com/excel/formulas/vlookup.php
Jul 29, 2008 at 10:10 PM
Here is my final VLOOKUP function. As noted, it does not handle approximate matches since a) I'm not able to find a detailed specification of how to handle string comparisons in Excel and b) I don't need this functionality for myself currently.

/**
* VLOOKUP
* The VLOOKUP function searches for value in the left-most column of lookup_array and returns the value in the same row based on the index_number.
* @param lookup_value The value that you want to match in lookup_array
* @param lookup_array The range of cells being searched
* @param index_number The column number in table_array from which the matching value must be returned. The first column is 1.
* @param not_exact_match Determines if you are looking for an exact match based on lookup_value.
* @return mixed The value of the found cell
*/
public static function VLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match=true) {
// index_number must be greater than or equal to 1
if ($index_number < 1) {
return self::$_errorCodes['value'];
}
// index_number must be less than or equal to the number of columns in lookup_array
if ($index_number > count($lookup_array)) {
return self::$_errorCodes['reference'];
}
// re-index lookup_array with numeric keys starting at 1
array_unshift($lookup_array, array());
$lookup_array = array_slice(array_values($lookup_array), 1, count($lookup_array), true);
// look for an exact match
$row_number = array_search($lookup_value, $lookup_array[1]);
// if an exact match is required, we have what we need to return an appropriate response
if ($not_exact_match == false) {
if ($row_number === false) {
return self::$_errorCodes['na'];
} else {
return $lookup_array[$index_number][$row_number];
}
}
// TODO: The VLOOKUP spec in Excel states that, at this point, we should search for
// the highest value that is less than lookup_value. However, documentation on how string
// values should be treated here is sparse.
return self::$_errorCodes['na'];
}


Coordinator
Aug 11, 2008 at 1:16 PM
Function has been added as-is (http://www.codeplex.com/PHPExcel/WorkItem/View.aspx?WorkItemId=7270)