[Summary view]

Differences Between: [Versions 400 and 401] [Versions 401 and 403]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Calculation\LookupRef;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Exception;
   7  use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
   8  use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
   9  
  10  class VLookup extends LookupBase
  11  {
  12      use ArrayEnabled;
  13  
  14      /**
  15       * VLOOKUP
  16       * The VLOOKUP function searches for value in the left-most column of lookup_array and returns the value
  17       *     in the same row based on the index_number.
  18       *
  19       * @param mixed $lookupValue The value that you want to match in lookup_array
  20       * @param mixed $lookupArray The range of cells being searched
  21       * @param mixed $indexNumber The column number in table_array from which the matching value must be returned.
  22       *                                The first column is 1.
  23       * @param mixed $notExactMatch determines if you are looking for an exact match based on lookup_value
  24       *
  25       * @return mixed The value of the found cell
  26       */
  27      public static function lookup($lookupValue, $lookupArray, $indexNumber, $notExactMatch = true)
  28      {
  29          if (is_array($lookupValue)) {
  30              return self::evaluateArrayArgumentsIgnore([self::class, __FUNCTION__], 1, $lookupValue, $lookupArray, $indexNumber, $notExactMatch);
  31          }
  32  
  33          $notExactMatch = (bool) ($notExactMatch ?? true);
  34  
  35          try {
  36              self::validateLookupArray($lookupArray);
  37              $indexNumber = self::validateIndexLookup($lookupArray, $indexNumber);
  38          } catch (Exception $e) {
  39              return $e->getMessage();
  40          }
  41  
  42          $f = array_keys($lookupArray);
  43          $firstRow = array_pop($f);
  44          if ((!is_array($lookupArray[$firstRow])) || ($indexNumber > count($lookupArray[$firstRow]))) {
  45              return ExcelError::REF();
  46          }
  47          $columnKeys = array_keys($lookupArray[$firstRow]);
  48          $returnColumn = $columnKeys[--$indexNumber];
  49          $firstColumn = array_shift($columnKeys) ?? 1;
  50  
  51          if (!$notExactMatch) {
  52              /** @var callable */
  53              $callable = [self::class, 'vlookupSort'];
  54              uasort($lookupArray, $callable);
  55          }
  56  
  57          $rowNumber = self::vLookupSearch($lookupValue, $lookupArray, $firstColumn, $notExactMatch);
  58  
  59          if ($rowNumber !== null) {
  60              // return the appropriate value
  61              return $lookupArray[$rowNumber][$returnColumn];
  62          }
  63  
  64          return ExcelError::NA();
  65      }
  66  
  67      private static function vlookupSort(array $a, array $b): int
  68      {
  69          reset($a);
  70          $firstColumn = key($a);
  71          $aLower = StringHelper::strToLower((string) $a[$firstColumn]);
  72          $bLower = StringHelper::strToLower((string) $b[$firstColumn]);
  73  
  74          if ($aLower == $bLower) {
  75              return 0;
  76          }
  77  
  78          return ($aLower < $bLower) ? -1 : 1;
  79      }
  80  
  81      /**
  82       * @param mixed $lookupValue The value that you want to match in lookup_array
  83       * @param  int|string $column
  84       */
  85      private static function vLookupSearch($lookupValue, array $lookupArray, $column, bool $notExactMatch): ?int
  86      {
  87          $lookupLower = StringHelper::strToLower((string) $lookupValue);
  88  
  89          $rowNumber = null;
  90          foreach ($lookupArray as $rowKey => $rowData) {
  91              $bothNumeric = is_numeric($lookupValue) && is_numeric($rowData[$column]);
  92              $bothNotNumeric = !is_numeric($lookupValue) && !is_numeric($rowData[$column]);
  93              $cellDataLower = StringHelper::strToLower((string) $rowData[$column]);
  94  
  95              // break if we have passed possible keys
  96              if (
  97                  $notExactMatch &&
  98                  (($bothNumeric && ($rowData[$column] > $lookupValue)) ||
  99                  ($bothNotNumeric && ($cellDataLower > $lookupLower)))
 100              ) {
 101                  break;
 102              }
 103  
 104              $rowNumber = self::checkMatch(
 105                  $bothNumeric,
 106                  $bothNotNumeric,
 107                  $notExactMatch,
 108                  $rowKey,
 109                  $cellDataLower,
 110                  $lookupLower,
 111                  $rowNumber
 112              );
 113          }
 114  
 115          return $rowNumber;
 116      }
 117  }