HLOOKUP function PHPEXCEl not working with dynamic data: Workaround

Topics: Developer Forum
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 top-most 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).
Jun 19, 2013 at 9:48 AM
Cool! Thanx for the update!!!