public static function PHPExcel_Calculation_LookupRef::HLOOKUP in Loft Data Grids 7.2

Same name and namespace in other branches

1. 6.2 vendor/phpoffice/phpexcel/Classes/PHPExcel/Calculation/LookupRef.php \PHPExcel_Calculation_LookupRef::HLOOKUP()

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.

Parameters

lookup_value The value that you want to match in lookup_array:

lookup_array The range of cells being searched:

index_number The row number in table_array from which the matching value must be returned. The first row is 1.:

not_exact_match Determines if you are looking for an exact match based on lookup_value.:

Return value

mixed The value of the found cell

File

vendor/phpoffice/phpexcel/Classes/PHPExcel/Calculation/LookupRef.php, line 755

Class

PHPExcel_Calculation_LookupRef

PHPExcel_Calculation_LookupRef

Code

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 (is_numeric($lookup_value) && is_numeric($rowData) && $rowData > $lookup_value || !is_numeric($lookup_value) && !is_numeric($rowData) && 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();
}