[Summary view]

Differences Between: [Versions 400 and 401] [Versions 401 and 402] [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\Information\ExcelError;
   7  use PhpOffice\PhpSpreadsheet\Calculation\LookupRef;
   8  
   9  class Lookup
  10  {
  11      use ArrayEnabled;
  12  
  13      /**
  14       * LOOKUP
  15       * The LOOKUP function searches for value either from a one-row or one-column range or from an array.
  16       *
  17       * @param mixed $lookupValue The value that you want to match in lookup_array
  18       * @param mixed $lookupVector The range of cells being searched
  19       * @param null|mixed $resultVector The column from which the matching value must be returned
  20       *
  21       * @return mixed The value of the found cell
  22       */
  23      public static function lookup($lookupValue, $lookupVector, $resultVector = null)
  24      {
  25          if (is_array($lookupValue)) {
  26              return self::evaluateArrayArgumentsSubset([self::class, __FUNCTION__], 1, $lookupValue, $lookupVector, $resultVector);
  27          }
  28  
  29          if (!is_array($lookupVector)) {
  30              return ExcelError::NA();
  31          }
  32          $hasResultVector = isset($resultVector);
  33          $lookupRows = self::rowCount($lookupVector);
  34          $lookupColumns = self::columnCount($lookupVector);
  35          // we correctly orient our results
  36          if (($lookupRows === 1 && $lookupColumns > 1) || (!$hasResultVector && $lookupRows === 2 && $lookupColumns !== 2)) {
  37              $lookupVector = LookupRef\Matrix::transpose($lookupVector);
  38              $lookupRows = self::rowCount($lookupVector);
  39              $lookupColumns = self::columnCount($lookupVector);
  40          }
  41  
  42          $resultVector = self::verifyResultVector($lookupVector, $resultVector);
  43  
  44          if ($lookupRows === 2 && !$hasResultVector) {
  45              $resultVector = array_pop($lookupVector);
  46              $lookupVector = array_shift($lookupVector);
  47          }
  48  
  49          if ($lookupColumns !== 2) {
  50              $lookupVector = self::verifyLookupValues($lookupVector, $resultVector);
  51          }
  52  
  53          return VLookup::lookup($lookupValue, $lookupVector, 2);
  54      }
  55  
  56      private static function verifyLookupValues(array $lookupVector, array $resultVector): array
  57      {
  58          foreach ($lookupVector as &$value) {
  59              if (is_array($value)) {
  60                  $k = array_keys($value);
  61                  $key1 = $key2 = array_shift($k);
  62                  ++$key2;
  63                  $dataValue1 = $value[$key1];
  64              } else {
  65                  $key1 = 0;
  66                  $key2 = 1;
  67                  $dataValue1 = $value;
  68              }
  69  
  70              $dataValue2 = array_shift($resultVector);
  71              if (is_array($dataValue2)) {
  72                  $dataValue2 = array_shift($dataValue2);
  73              }
  74              $value = [$key1 => $dataValue1, $key2 => $dataValue2];
  75          }
  76          unset($value);
  77  
  78          return $lookupVector;
  79      }
  80  
  81      private static function verifyResultVector(array $lookupVector, $resultVector)
  82      {
  83          if ($resultVector === null) {
  84              $resultVector = $lookupVector;
  85          }
  86  
  87          $resultRows = self::rowCount($resultVector);
  88          $resultColumns = self::columnCount($resultVector);
  89  
  90          // we correctly orient our results
  91          if ($resultRows === 1 && $resultColumns > 1) {
  92              $resultVector = LookupRef\Matrix::transpose($resultVector);
  93          }
  94  
  95          return $resultVector;
  96      }
  97  
  98      private static function rowCount(array $dataArray): int
  99      {
 100          return count($dataArray);
 101      }
 102  
 103      private static function columnCount(array $dataArray): int
 104      {
 105          $rowKeys = array_keys($dataArray);
 106          $row = array_shift($rowKeys);
 107  
 108          return count($dataArray[$row]);
 109      }
 110  }