Jun 4, 2013 at 2:00 PM
Edited Jun 4, 2013 at 3:03 PM

Hello,
I'm using PHPExcel to calculate data for a client. The excel file is using the HLOOKUP many times to get the right data. It is used like:
=HLOOKUP(INPUT!B6;E231:J233;3;0).
Where INPUT!B6 refers to an sheet where the value is populated from a webform. This field is set like:
$value = dynamic data from webform filled in by users
$objWorksheetInput>setCellValueByColumnAndRow(1, $row, $value );
But HLOOKUP is not working in PHPEXCEL if the reference data is populated with setCellValue.
EXAMPLE
The excel sheet INPUT B6 has the default value of 1.
If the user fill in via the webform number 2 the HLOOKUP will calculate with the value of 1 in het HLOOKUP function.
I hope anyone has a solution!
I know there is a VLOOKUP functions. Is it hard to edit (transform) this function to a HLOOKUP function?
Greetings Chris


Coordinator
Jun 4, 2013 at 10:36 PM

Probably isn't too hard, but it isn't my highest priority... but feel free to have a go a writing it yourself and perhaps submitting as a Pull Request to the github repository


Jun 12, 2013 at 2:38 PM
Edited Jun 12, 2013 at 2:39 PM

Check below, for anyone is looking for the HLOOKUP function here it is. It's the VLOOKUP function with some tweaks...
/**
* HLOOKUP
* The HLOOKUP function searches for value in the topmost row of lookup_array and returns the value in the same column 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 row number in table_array from which the matching value must be returned. The first row 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 HLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match=true) {
$lookup_value = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value);
$index_number = PHPExcel_Calculation_Functions::flattenSingleValue($index_number);
$not_exact_match = PHPExcel_Calculation_Functions::flattenSingleValue($not_exact_match);
// index_number must be greater than or equal to 1
if ($index_number < 1) {
return PHPExcel_Calculation_Functions::VALUE();
}
// index_number must be less than or equal to the number of columns in lookup_array
if ((!is_array($lookup_array))  (empty($lookup_array))) {
return PHPExcel_Calculation_Functions::REF();
} else {
$f = array_keys($lookup_array);
$firstRow = array_pop($f);
if ((!is_array($lookup_array[$firstRow]))  ($index_number > count($lookup_array[$firstRow]))) {
return PHPExcel_Calculation_Functions::REF();
} else {
$columnKeys = array_keys($lookup_array[$firstRow]);
$firstkey = $f[0]  1;
$returnColumn = $firstkey + $index_number;
$firstColumn = array_shift($f);
}
}
if (!$not_exact_match) {
$firstRowH = asort($lookup_array[$firstColumn]);
}
$rowNumber = $rowValue = False;
foreach($lookup_array[$firstColumn] as $rowKey => $rowData) {
if (strtolower($rowData) > strtolower($lookup_value)) {
break;
}
$rowNumber = $rowKey;
$rowValue = $rowData;
}
if ($rowNumber !== false) {
if ((!$not_exact_match) && ($rowValue != $lookup_value)) {
// if an exact match is required, we have what we need to return an appropriate response
return PHPExcel_Calculation_Functions::NA();
} else {
// otherwise return the appropriate value
return $lookup_array[$returnColumn][$rowNumber];
}
}
return PHPExcel_Calculation_Functions::NA();
} // function HLOOKUP()


Coordinator
Jun 14, 2013 at 12:44 PM

Thanks for this contribution, I'll take a look


Coordinator
Jun 15, 2013 at 8:37 PM

I had to make a couple of minor tweaks, but HLOOKUP is now in the github repo... also highlighted an issue with VLOOKUP, and I've fixed both. Neither is perfect, but they're accurate in most cases... I'll take a closer look over the next few days to see
if I can tidy up the edge cases (such as strings matching against numeric zero).



Cool! Thanx for the update!!!

