Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
  • Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
  • PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.

Differences Between: [Versions 310 and 311] [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403] [Versions 39 and 311]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Calculation;
   4  
   5  use PhpOffice\PhpSpreadsheet\Cell\Cell;
   6  use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
   7  use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
   8  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
   9  
  10  class LookupRef
  11  {
  12      /**
  13       * CELL_ADDRESS.
  14       *
  15       * Creates a cell address as text, given specified row and column numbers.
  16       *
  17       * Excel Function:
  18       *        =ADDRESS(row, column, [relativity], [referenceStyle], [sheetText])
  19       *
  20       * @param mixed $row Row number to use in the cell reference
  21       * @param mixed $column Column number to use in the cell reference
  22       * @param int $relativity Flag indicating the type of reference to return
  23       *                                1 or omitted    Absolute
  24       *                                2                Absolute row; relative column
  25       *                                3                Relative row; absolute column
  26       *                                4                Relative
  27       * @param bool $referenceStyle A logical value that specifies the A1 or R1C1 reference style.
  28       *                                TRUE or omitted        CELL_ADDRESS returns an A1-style reference
  29       *                                FALSE                CELL_ADDRESS returns an R1C1-style reference
  30       * @param string $sheetText Optional Name of worksheet to use
  31       *
  32       * @return string
  33       */
  34      public static function cellAddress($row, $column, $relativity = 1, $referenceStyle = true, $sheetText = '')
  35      {
  36          $row = Functions::flattenSingleValue($row);
  37          $column = Functions::flattenSingleValue($column);
  38          $relativity = Functions::flattenSingleValue($relativity);
  39          $sheetText = Functions::flattenSingleValue($sheetText);
  40  
  41          if (($row < 1) || ($column < 1)) {
  42              return Functions::VALUE();
  43          }
  44  
  45          if ($sheetText > '') {
  46              if (strpos($sheetText, ' ') !== false) {
  47                  $sheetText = "'" . $sheetText . "'";
  48              }
  49              $sheetText .= '!';
  50          }
  51          if ((!is_bool($referenceStyle)) || $referenceStyle) {
  52              $rowRelative = $columnRelative = '$';
  53              $column = Coordinate::stringFromColumnIndex($column);
  54              if (($relativity == 2) || ($relativity == 4)) {
  55                  $columnRelative = '';
  56              }
  57              if (($relativity == 3) || ($relativity == 4)) {
  58                  $rowRelative = '';
  59              }
  60  
  61              return $sheetText . $columnRelative . $column . $rowRelative . $row;
  62          }
  63          if (($relativity == 2) || ($relativity == 4)) {
  64              $column = '[' . $column . ']';
  65          }
  66          if (($relativity == 3) || ($relativity == 4)) {
  67              $row = '[' . $row . ']';
  68          }
  69  
  70          return $sheetText . 'R' . $row . 'C' . $column;
  71      }
  72  
  73      /**
  74       * COLUMN.
  75       *
  76       * Returns the column number of the given cell reference
  77       * If the cell reference is a range of cells, COLUMN returns the column numbers of each column in the reference as a horizontal array.
  78       * If cell reference is omitted, and the function is being called through the calculation engine, then it is assumed to be the
  79       *        reference of the cell in which the COLUMN function appears; otherwise this function returns 0.
  80       *
  81       * Excel Function:
  82       *        =COLUMN([cellAddress])
  83       *
  84       * @param null|array|string $cellAddress A reference to a range of cells for which you want the column numbers
  85       *
  86       * @return int|int[]
  87       */
  88      public static function COLUMN($cellAddress = null)
  89      {
  90          if ($cellAddress === null || trim($cellAddress) === '') {
  91              return 0;
  92          }
  93  
  94          if (is_array($cellAddress)) {
  95              foreach ($cellAddress as $columnKey => $value) {
  96                  $columnKey = preg_replace('/[^a-z]/i', '', $columnKey);
  97  
  98                  return (int) Coordinate::columnIndexFromString($columnKey);
  99              }
 100          } else {
 101              [$sheet, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true);
 102              if (strpos($cellAddress, ':') !== false) {
 103                  [$startAddress, $endAddress] = explode(':', $cellAddress);
 104                  $startAddress = preg_replace('/[^a-z]/i', '', $startAddress);
 105                  $endAddress = preg_replace('/[^a-z]/i', '', $endAddress);
 106                  $returnValue = [];
 107                  do {
 108                      $returnValue[] = (int) Coordinate::columnIndexFromString($startAddress);
 109                  } while ($startAddress++ != $endAddress);
 110  
 111                  return $returnValue;
 112              }
 113              $cellAddress = preg_replace('/[^a-z]/i', '', $cellAddress);
 114  
 115              return (int) Coordinate::columnIndexFromString($cellAddress);
 116          }
 117      }
 118  
 119      /**
 120       * COLUMNS.
 121       *
 122       * Returns the number of columns in an array or reference.
 123       *
 124       * Excel Function:
 125       *        =COLUMNS(cellAddress)
 126       *
 127       * @param null|array|string $cellAddress An array or array formula, or a reference to a range of cells for which you want the number of columns
 128       *
 129       * @return int|string The number of columns in cellAddress, or a string if arguments are invalid
 130       */
 131      public static function COLUMNS($cellAddress = null)
 132      {
 133          if ($cellAddress === null || $cellAddress === '') {
 134              return 1;
 135          } elseif (!is_array($cellAddress)) {
 136              return Functions::VALUE();
 137          }
 138  
 139          reset($cellAddress);
 140          $isMatrix = (is_numeric(key($cellAddress)));
 141          [$columns, $rows] = Calculation::getMatrixDimensions($cellAddress);
 142  
 143          if ($isMatrix) {
 144              return $rows;
 145          }
 146  
 147          return $columns;
 148      }
 149  
 150      /**
 151       * ROW.
 152       *
 153       * Returns the row number of the given cell reference
 154       * If the cell reference is a range of cells, ROW returns the row numbers of each row in the reference as a vertical array.
 155       * If cell reference is omitted, and the function is being called through the calculation engine, then it is assumed to be the
 156       *        reference of the cell in which the ROW function appears; otherwise this function returns 0.
 157       *
 158       * Excel Function:
 159       *        =ROW([cellAddress])
 160       *
 161       * @param null|array|string $cellAddress A reference to a range of cells for which you want the row numbers
 162       *
 163       * @return int|mixed[]|string
 164       */
 165      public static function ROW($cellAddress = null)
 166      {
 167          if ($cellAddress === null || trim($cellAddress) === '') {
 168              return 0;
 169          }
 170  
 171          if (is_array($cellAddress)) {
 172              foreach ($cellAddress as $columnKey => $rowValue) {
 173                  foreach ($rowValue as $rowKey => $cellValue) {
 174                      return (int) preg_replace('/\D/', '', $rowKey);
 175                  }
 176              }
 177          } else {
 178              [$sheet, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true);
 179              if (strpos($cellAddress, ':') !== false) {
 180                  [$startAddress, $endAddress] = explode(':', $cellAddress);
 181                  $startAddress = preg_replace('/\D/', '', $startAddress);
 182                  $endAddress = preg_replace('/\D/', '', $endAddress);
 183                  $returnValue = [];
 184                  do {
 185                      $returnValue[][] = (int) $startAddress;
 186                  } while ($startAddress++ != $endAddress);
 187  
 188                  return $returnValue;
 189              }
 190              [$cellAddress] = explode(':', $cellAddress);
 191  
 192              return (int) preg_replace('/\D/', '', $cellAddress);
 193          }
 194      }
 195  
 196      /**
 197       * ROWS.
 198       *
 199       * Returns the number of rows in an array or reference.
 200       *
 201       * Excel Function:
 202       *        =ROWS(cellAddress)
 203       *
 204       * @param null|array|string $cellAddress An array or array formula, or a reference to a range of cells for which you want the number of rows
 205       *
 206       * @return int|string The number of rows in cellAddress, or a string if arguments are invalid
 207       */
 208      public static function ROWS($cellAddress = null)
 209      {
 210          if ($cellAddress === null || $cellAddress === '') {
 211              return 1;
 212          } elseif (!is_array($cellAddress)) {
 213              return Functions::VALUE();
 214          }
 215  
 216          reset($cellAddress);
 217          $isMatrix = (is_numeric(key($cellAddress)));
 218          [$columns, $rows] = Calculation::getMatrixDimensions($cellAddress);
 219  
 220          if ($isMatrix) {
 221              return $columns;
 222          }
 223  
 224          return $rows;
 225      }
 226  
 227      /**
 228       * HYPERLINK.
 229       *
 230       * Excel Function:
 231       *        =HYPERLINK(linkURL,displayName)
 232       *
 233       * @param string $linkURL Value to check, is also the value returned when no error
 234       * @param string $displayName Value to return when testValue is an error condition
 235       * @param Cell $pCell The cell to set the hyperlink in
 236       *
 237       * @return mixed The value of $displayName (or $linkURL if $displayName was blank)
 238       */
 239      public static function HYPERLINK($linkURL = '', $displayName = null, ?Cell $pCell = null)
 240      {
 241          $linkURL = ($linkURL === null) ? '' : Functions::flattenSingleValue($linkURL);
 242          $displayName = ($displayName === null) ? '' : Functions::flattenSingleValue($displayName);
 243  
 244          if ((!is_object($pCell)) || (trim($linkURL) == '')) {
 245              return Functions::REF();
 246          }
 247  
 248          if ((is_object($displayName)) || trim($displayName) == '') {
 249              $displayName = $linkURL;
 250          }
 251  
 252          $pCell->getHyperlink()->setUrl($linkURL);
 253          $pCell->getHyperlink()->setTooltip($displayName);
 254  
 255          return $displayName;
 256      }
 257  
 258      /**
 259       * INDIRECT.
 260       *
 261       * Returns the reference specified by a text string.
 262       * References are immediately evaluated to display their contents.
 263       *
 264       * Excel Function:
 265       *        =INDIRECT(cellAddress)
 266       *
 267       * NOTE - INDIRECT() does not yet support the optional a1 parameter introduced in Excel 2010
 268       *
 269       * @param null|array|string $cellAddress $cellAddress The cell address of the current cell (containing this formula)
 270       * @param Cell $pCell The current cell (containing this formula)
 271       *
 272       * @return mixed The cells referenced by cellAddress
 273       *
 274       * @TODO    Support for the optional a1 parameter introduced in Excel 2010
 275       */
 276      public static function INDIRECT($cellAddress = null, ?Cell $pCell = null)
 277      {
 278          $cellAddress = Functions::flattenSingleValue($cellAddress);
 279          if ($cellAddress === null || $cellAddress === '') {
 280              return Functions::REF();
 281          }
 282  
 283          $cellAddress1 = $cellAddress;
 284          $cellAddress2 = null;
 285          if (strpos($cellAddress, ':') !== false) {
 286              [$cellAddress1, $cellAddress2] = explode(':', $cellAddress);
 287          }
 288  
 289          if (
 290              (!preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $cellAddress1, $matches)) ||
 291              (($cellAddress2 !== null) && (!preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $cellAddress2, $matches)))
 292          ) {
 293              if (!preg_match('/^' . Calculation::CALCULATION_REGEXP_DEFINEDNAME . '$/i', $cellAddress1, $matches)) {
 294                  return Functions::REF();
 295              }
 296  
 297              if (strpos($cellAddress, '!') !== false) {
 298                  [$sheetName, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true);
 299                  $sheetName = trim($sheetName, "'");
 300                  $pSheet = $pCell->getWorksheet()->getParent()->getSheetByName($sheetName);
 301              } else {
 302                  $pSheet = $pCell->getWorksheet();
 303              }
 304  
 305              return Calculation::getInstance()->extractNamedRange($cellAddress, $pSheet, false);
 306          }
 307  
 308          if (strpos($cellAddress, '!') !== false) {
 309              [$sheetName, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true);
 310              $sheetName = trim($sheetName, "'");
 311              $pSheet = $pCell->getWorksheet()->getParent()->getSheetByName($sheetName);
 312          } else {
 313              $pSheet = $pCell->getWorksheet();
 314          }
 315  
 316          return Calculation::getInstance()->extractCellRange($cellAddress, $pSheet, false);
 317      }
 318  
 319      /**
 320       * OFFSET.
 321       *
 322       * Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
 323       * The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and
 324       * the number of columns to be returned.
 325       *
 326       * Excel Function:
 327       *        =OFFSET(cellAddress, rows, cols, [height], [width])
 328       *
 329       * @param null|string $cellAddress The reference from which you want to base the offset. Reference must refer to a cell or
 330       *                                range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
 331       * @param mixed $rows The number of rows, up or down, that you want the upper-left cell to refer to.
 332       *                                Using 5 as the rows argument specifies that the upper-left cell in the reference is
 333       *                                five rows below reference. Rows can be positive (which means below the starting reference)
 334       *                                or negative (which means above the starting reference).
 335       * @param mixed $columns The number of columns, to the left or right, that you want the upper-left cell of the result
 336       *                                to refer to. Using 5 as the cols argument specifies that the upper-left cell in the
 337       *                                reference is five columns to the right of reference. Cols can be positive (which means
 338       *                                to the right of the starting reference) or negative (which means to the left of the
 339       *                                starting reference).
 340       * @param mixed $height The height, in number of rows, that you want the returned reference to be. Height must be a positive number.
 341       * @param mixed $width The width, in number of columns, that you want the returned reference to be. Width must be a positive number.
 342       *
 343       * @return string A reference to a cell or range of cells
 344       */
 345      public static function OFFSET($cellAddress = null, $rows = 0, $columns = 0, $height = null, $width = null, ?Cell $pCell = null)
 346      {
 347          $rows = Functions::flattenSingleValue($rows);
 348          $columns = Functions::flattenSingleValue($columns);
 349          $height = Functions::flattenSingleValue($height);
 350          $width = Functions::flattenSingleValue($width);
 351          if ($cellAddress === null) {
 352              return 0;
 353          }
 354  
 355          if (!is_object($pCell)) {
 356              return Functions::REF();
 357          }
 358  
 359          $sheetName = null;
 360          if (strpos($cellAddress, '!')) {
 361              [$sheetName, $cellAddress] = Worksheet::extractSheetTitle($cellAddress, true);
 362              $sheetName = trim($sheetName, "'");
 363          }
 364          if (strpos($cellAddress, ':')) {
 365              [$startCell, $endCell] = explode(':', $cellAddress);
 366          } else {
 367              $startCell = $endCell = $cellAddress;
 368          }
 369          [$startCellColumn, $startCellRow] = Coordinate::coordinateFromString($startCell);
 370          [$endCellColumn, $endCellRow] = Coordinate::coordinateFromString($endCell);
 371  
 372          $startCellRow += $rows;
 373          $startCellColumn = Coordinate::columnIndexFromString($startCellColumn) - 1;
 374          $startCellColumn += $columns;
 375  
 376          if (($startCellRow <= 0) || ($startCellColumn < 0)) {
 377              return Functions::REF();
 378          }
 379          $endCellColumn = Coordinate::columnIndexFromString($endCellColumn) - 1;
 380          if (($width != null) && (!is_object($width))) {
 381              $endCellColumn = $startCellColumn + $width - 1;
 382          } else {
 383              $endCellColumn += $columns;
 384          }
 385          $startCellColumn = Coordinate::stringFromColumnIndex($startCellColumn + 1);
 386  
 387          if (($height != null) && (!is_object($height))) {
 388              $endCellRow = $startCellRow + $height - 1;
 389          } else {
 390              $endCellRow += $rows;
 391          }
 392  
 393          if (($endCellRow <= 0) || ($endCellColumn < 0)) {
 394              return Functions::REF();
 395          }
 396          $endCellColumn = Coordinate::stringFromColumnIndex($endCellColumn + 1);
 397  
 398          $cellAddress = $startCellColumn . $startCellRow;
 399          if (($startCellColumn != $endCellColumn) || ($startCellRow != $endCellRow)) {
 400              $cellAddress .= ':' . $endCellColumn . $endCellRow;
 401          }
 402  
 403          if ($sheetName !== null) {
 404              $pSheet = $pCell->getWorksheet()->getParent()->getSheetByName($sheetName);
 405          } else {
 406              $pSheet = $pCell->getWorksheet();
 407          }
 408  
 409          return Calculation::getInstance()->extractCellRange($cellAddress, $pSheet, false);
 410      }
 411  
 412      /**
 413       * CHOOSE.
 414       *
 415       * Uses lookup_value to return a value from the list of value arguments.
 416       * Use CHOOSE to select one of up to 254 values based on the lookup_value.
 417       *
 418       * Excel Function:
 419       *        =CHOOSE(index_num, value1, [value2], ...)
 420       *
 421       * @return mixed The selected value
 422       */
 423      public static function CHOOSE(...$chooseArgs)
 424      {
 425          $chosenEntry = Functions::flattenArray(array_shift($chooseArgs));
 426          $entryCount = count($chooseArgs) - 1;
 427  
 428          if (is_array($chosenEntry)) {
 429              $chosenEntry = array_shift($chosenEntry);
 430          }
 431          if ((is_numeric($chosenEntry)) && (!is_bool($chosenEntry))) {
 432              --$chosenEntry;
 433          } else {
 434              return Functions::VALUE();
 435          }
 436          $chosenEntry = floor($chosenEntry);
 437          if (($chosenEntry < 0) || ($chosenEntry > $entryCount)) {
 438              return Functions::VALUE();
 439          }
 440  
 441          if (is_array($chooseArgs[$chosenEntry])) {
 442              return Functions::flattenArray($chooseArgs[$chosenEntry]);
 443          }
 444  
 445          return $chooseArgs[$chosenEntry];
 446      }
 447  
 448      /**
 449       * MATCH.
 450       *
 451       * The MATCH function searches for a specified item in a range of cells
 452       *
 453       * Excel Function:
 454       *        =MATCH(lookup_value, lookup_array, [match_type])
 455       *
 456       * @param mixed $lookupValue The value that you want to match in lookup_array
 457       * @param mixed $lookupArray The range of cells being searched
 458       * @param mixed $matchType The number -1, 0, or 1. -1 means above, 0 means exact match, 1 means below.
 459       *                         If match_type is 1 or -1, the list has to be ordered.
 460       *
 461       * @return int|string The relative position of the found item
 462       */
 463      public static function MATCH($lookupValue, $lookupArray, $matchType = 1)
 464      {
 465          $lookupArray = Functions::flattenArray($lookupArray);
 466          $lookupValue = Functions::flattenSingleValue($lookupValue);
 467          $matchType = ($matchType === null) ? 1 : (int) Functions::flattenSingleValue($matchType);
 468  
 469          // MATCH is not case sensitive, so we convert lookup value to be lower cased in case it's string type.
 470          if (is_string($lookupValue)) {
 471              $lookupValue = StringHelper::strToLower($lookupValue);
 472          }
 473  
 474          // Lookup_value type has to be number, text, or logical values
 475          if ((!is_numeric($lookupValue)) && (!is_string($lookupValue)) && (!is_bool($lookupValue))) {
 476              return Functions::NA();
 477          }
 478  
 479          // Match_type is 0, 1 or -1
 480          if (($matchType !== 0) && ($matchType !== -1) && ($matchType !== 1)) {
 481              return Functions::NA();
 482          }
 483  
 484          // Lookup_array should not be empty
 485          $lookupArraySize = count($lookupArray);
 486          if ($lookupArraySize <= 0) {
 487              return Functions::NA();
 488          }
 489  
 490          if ($matchType == 1) {
 491              // If match_type is 1 the list has to be processed from last to first
 492  
 493              $lookupArray = array_reverse($lookupArray);
 494              $keySet = array_reverse(array_keys($lookupArray));
 495          }
 496  
 497          // Lookup_array should contain only number, text, or logical values, or empty (null) cells
 498          foreach ($lookupArray as $i => $lookupArrayValue) {
 499              //    check the type of the value
 500              if (
 501                  (!is_numeric($lookupArrayValue)) && (!is_string($lookupArrayValue)) &&
 502                  (!is_bool($lookupArrayValue)) && ($lookupArrayValue !== null)
 503              ) {
 504                  return Functions::NA();
 505              }
 506              // Convert strings to lowercase for case-insensitive testing
 507              if (is_string($lookupArrayValue)) {
 508                  $lookupArray[$i] = StringHelper::strToLower($lookupArrayValue);
 509              }
 510              if (($lookupArrayValue === null) && (($matchType == 1) || ($matchType == -1))) {
 511                  unset($lookupArray[$i]);
 512              }
 513          }
 514  
 515          // **
 516          // find the match
 517          // **
 518  
 519          if ($matchType === 0 || $matchType === 1) {
 520              foreach ($lookupArray as $i => $lookupArrayValue) {
 521                  $typeMatch = ((gettype($lookupValue) === gettype($lookupArrayValue)) || (is_numeric($lookupValue) && is_numeric($lookupArrayValue)));
 522                  $exactTypeMatch = $typeMatch && $lookupArrayValue === $lookupValue;
 523                  $nonOnlyNumericExactMatch = !$typeMatch && $lookupArrayValue === $lookupValue;
 524                  $exactMatch = $exactTypeMatch || $nonOnlyNumericExactMatch;
 525  
 526                  if ($matchType === 0) {
 527                      if ($typeMatch && is_string($lookupValue) && (bool) preg_match('/([\?\*])/', $lookupValue)) {
 528                          $splitString = $lookupValue;
 529                          $chars = array_map(function ($i) use ($splitString) {
 530                              return mb_substr($splitString, $i, 1);
 531                          }, range(0, mb_strlen($splitString) - 1));
 532  
 533                          $length = count($chars);
 534                          $pattern = '/^';
 535                          for ($j = 0; $j < $length; ++$j) {
 536                              if ($chars[$j] === '~') {
 537                                  if (isset($chars[$j + 1])) {
 538                                      if ($chars[$j + 1] === '*') {
 539                                          $pattern .= preg_quote($chars[$j + 1], '/');
 540                                          ++$j;
 541                                      } elseif ($chars[$j + 1] === '?') {
 542                                          $pattern .= preg_quote($chars[$j + 1], '/');
 543                                          ++$j;
 544                                      }
 545                                  } else {
 546                                      $pattern .= preg_quote($chars[$j], '/');
 547                                  }
 548                              } elseif ($chars[$j] === '*') {
 549                                  $pattern .= '.*';
 550                              } elseif ($chars[$j] === '?') {
 551                                  $pattern .= '.{1}';
 552                              } else {
 553                                  $pattern .= preg_quote($chars[$j], '/');
 554                              }
 555                          }
 556  
 557                          $pattern .= '$/';
 558                          if ((bool) preg_match($pattern, $lookupArrayValue)) {
 559                              // exact match
 560                              return $i + 1;
 561                          }
 562                      } elseif ($exactMatch) {
 563                          // exact match
 564                          return $i + 1;
 565                      }
 566                  } elseif (($matchType === 1) && $typeMatch && ($lookupArrayValue <= $lookupValue)) {
 567                      $i = array_search($i, $keySet);
 568  
 569                      // The current value is the (first) match
 570                      return $i + 1;
 571                  }
 572              }
 573          } else {
 574              $maxValueKey = null;
 575  
 576              // The basic algorithm is:
 577              // Iterate and keep the highest match until the next element is smaller than the searched value.
 578              // Return immediately if perfect match is found
 579              foreach ($lookupArray as $i => $lookupArrayValue) {
 580                  $typeMatch = gettype($lookupValue) === gettype($lookupArrayValue);
 581                  $exactTypeMatch = $typeMatch && $lookupArrayValue === $lookupValue;
 582                  $nonOnlyNumericExactMatch = !$typeMatch && $lookupArrayValue === $lookupValue;
 583                  $exactMatch = $exactTypeMatch || $nonOnlyNumericExactMatch;
 584  
 585                  if ($exactMatch) {
 586                      // Another "special" case. If a perfect match is found,
 587                      // the algorithm gives up immediately
 588                      return $i + 1;
 589                  } elseif ($typeMatch & $lookupArrayValue >= $lookupValue) {
 590                      $maxValueKey = $i + 1;
 591                  } elseif ($typeMatch & $lookupArrayValue < $lookupValue) {
 592                      //Excel algorithm gives up immediately if the first element is smaller than the searched value
 593                      break;
 594                  }
 595              }
 596  
 597              if ($maxValueKey !== null) {
 598                  return $maxValueKey;
 599              }
 600          }
 601  
 602          // Unsuccessful in finding a match, return #N/A error value
 603          return Functions::NA();
 604      }
 605  
 606      /**
 607       * INDEX.
 608       *
 609       * Uses an index to choose a value from a reference or array
 610       *
 611       * Excel Function:
 612       *        =INDEX(range_array, row_num, [column_num])
 613       *
 614       * @param mixed $arrayValues A range of cells or an array constant
 615       * @param mixed $rowNum The row in array from which to return a value. If row_num is omitted, column_num is required.
 616       * @param mixed $columnNum The column in array from which to return a value. If column_num is omitted, row_num is required.
 617       *
 618       * @return mixed the value of a specified cell or array of cells
 619       */
 620      public static function INDEX($arrayValues, $rowNum = 0, $columnNum = 0)
 621      {
 622          $rowNum = Functions::flattenSingleValue($rowNum);
 623          $columnNum = Functions::flattenSingleValue($columnNum);
 624  
 625          if (($rowNum < 0) || ($columnNum < 0)) {
 626              return Functions::VALUE();
 627          }
 628  
 629          if (!is_array($arrayValues) || ($rowNum > count($arrayValues))) {
 630              return Functions::REF();
 631          }
 632  
 633          $rowKeys = array_keys($arrayValues);
 634          $columnKeys = @array_keys($arrayValues[$rowKeys[0]]);
 635  
 636          if ($columnNum > count($columnKeys)) {
 637              return Functions::VALUE();
 638          } elseif ($columnNum == 0) {
 639              if ($rowNum == 0) {
 640                  return $arrayValues;
 641              }
 642              $rowNum = $rowKeys[--$rowNum];
 643              $returnArray = [];
 644              foreach ($arrayValues as $arrayColumn) {
 645                  if (is_array($arrayColumn)) {
 646                      if (isset($arrayColumn[$rowNum])) {
 647                          $returnArray[] = $arrayColumn[$rowNum];
 648                      } else {
 649                          return [$rowNum => $arrayValues[$rowNum]];
 650                      }
 651                  } else {
 652                      return $arrayValues[$rowNum];
 653                  }
 654              }
 655  
 656              return $returnArray;
 657          }
 658          $columnNum = $columnKeys[--$columnNum];
 659          if ($rowNum > count($rowKeys)) {
 660              return Functions::VALUE();
 661          } elseif ($rowNum == 0) {
 662              return $arrayValues[$columnNum];
 663          }
 664          $rowNum = $rowKeys[--$rowNum];
 665  
 666          return $arrayValues[$rowNum][$columnNum];
 667      }
 668  
 669      /**
 670       * TRANSPOSE.
 671       *
 672       * @param array $matrixData A matrix of values
 673       *
 674       * @return array
 675       *
 676       * Unlike the Excel TRANSPOSE function, which will only work on a single row or column, this function will transpose a full matrix
 677       */
 678      public static function TRANSPOSE($matrixData)
 679      {
 680          $returnMatrix = [];
 681          if (!is_array($matrixData)) {
 682              $matrixData = [[$matrixData]];
 683          }
 684  
 685          $column = 0;
 686          foreach ($matrixData as $matrixRow) {
 687              $row = 0;
 688              foreach ($matrixRow as $matrixCell) {
 689                  $returnMatrix[$row][$column] = $matrixCell;
 690                  ++$row;
 691              }
 692              ++$column;
 693          }
 694  
 695          return $returnMatrix;
 696      }
 697  
 698      private static function vlookupSort($a, $b)
 699      {
 700          reset($a);
 701          $firstColumn = key($a);
 702          $aLower = StringHelper::strToLower($a[$firstColumn]);
 703          $bLower = StringHelper::strToLower($b[$firstColumn]);
 704          if ($aLower == $bLower) {
 705              return 0;
 706          }
 707  
 708          return ($aLower < $bLower) ? -1 : 1;
 709      }
 710  
 711      /**
 712       * VLOOKUP
 713       * The VLOOKUP function searches for value in the left-most column of lookup_array and returns the value in the same row based on the index_number.
 714       *
 715       * @param mixed $lookup_value The value that you want to match in lookup_array
 716       * @param mixed $lookup_array The range of cells being searched
 717       * @param mixed $index_number The column number in table_array from which the matching value must be returned. The first column is 1.
 718       * @param mixed $not_exact_match determines if you are looking for an exact match based on lookup_value
 719       *
 720       * @return mixed The value of the found cell
 721       */
 722      public static function VLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match = true)
 723      {
 724          $lookup_value = Functions::flattenSingleValue($lookup_value);
 725          $index_number = Functions::flattenSingleValue($index_number);
 726          $not_exact_match = Functions::flattenSingleValue($not_exact_match);
 727  
 728          // index_number must be greater than or equal to 1
 729          if ($index_number < 1) {
 730              return Functions::VALUE();
 731          }
 732  
 733          // index_number must be less than or equal to the number of columns in lookup_array
 734          if ((!is_array($lookup_array)) || (empty($lookup_array))) {
 735              return Functions::REF();
 736          }
 737          $f = array_keys($lookup_array);
 738          $firstRow = array_pop($f);
 739          if ((!is_array($lookup_array[$firstRow])) || ($index_number > count($lookup_array[$firstRow]))) {
 740              return Functions::REF();
 741          }
 742          $columnKeys = array_keys($lookup_array[$firstRow]);
 743          $returnColumn = $columnKeys[--$index_number];
 744          $firstColumn = array_shift($columnKeys);
 745  
 746          if (!$not_exact_match) {
 747              uasort($lookup_array, ['self', 'vlookupSort']);
 748          }
 749  
 750          $lookupLower = StringHelper::strToLower($lookup_value);
 751          $rowNumber = $rowValue = false;
 752          foreach ($lookup_array as $rowKey => $rowData) {
 753              $firstLower = StringHelper::strToLower($rowData[$firstColumn]);
 754  
 755              // break if we have passed possible keys
 756              if (
 757                  (is_numeric($lookup_value) && is_numeric($rowData[$firstColumn]) && ($rowData[$firstColumn] > $lookup_value)) ||
 758                  (!is_numeric($lookup_value) && !is_numeric($rowData[$firstColumn]) && ($firstLower > $lookupLower))
 759              ) {
 760                  break;
 761              }
 762              // remember the last key, but only if datatypes match
 763              if (
 764                  (is_numeric($lookup_value) && is_numeric($rowData[$firstColumn])) ||
 765                  (!is_numeric($lookup_value) && !is_numeric($rowData[$firstColumn]))
 766              ) {
 767                  if ($not_exact_match) {
 768                      $rowNumber = $rowKey;
 769  
 770                      continue;
 771                  } elseif (
 772                      ($firstLower == $lookupLower)
 773                      // Spreadsheets software returns first exact match,
 774                      // we have sorted and we might have broken key orders
 775                      // we want the first one (by its initial index)
 776                      && (($rowNumber == false) || ($rowKey < $rowNumber))
 777                  ) {
 778                      $rowNumber = $rowKey;
 779                  }
 780              }
 781          }
 782  
 783          if ($rowNumber !== false) {
 784              // return the appropriate value
 785              return $lookup_array[$rowNumber][$returnColumn];
 786          }
 787  
 788          return Functions::NA();
 789      }
 790  
 791      /**
 792       * HLOOKUP
 793       * 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.
 794       *
 795       * @param mixed $lookup_value The value that you want to match in lookup_array
 796       * @param mixed $lookup_array The range of cells being searched
 797       * @param mixed $index_number The row number in table_array from which the matching value must be returned. The first row is 1.
 798       * @param mixed $not_exact_match determines if you are looking for an exact match based on lookup_value
 799       *
 800       * @return mixed The value of the found cell
 801       */
 802      public static function HLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match = true)
 803      {
 804          $lookup_value = Functions::flattenSingleValue($lookup_value);
 805          $index_number = Functions::flattenSingleValue($index_number);
 806          $not_exact_match = Functions::flattenSingleValue($not_exact_match);
 807  
 808          // index_number must be greater than or equal to 1
 809          if ($index_number < 1) {
 810              return Functions::VALUE();
 811          }
 812  
 813          // index_number must be less than or equal to the number of columns in lookup_array
 814          if ((!is_array($lookup_array)) || (empty($lookup_array))) {
 815              return Functions::REF();
 816          }
 817          $f = array_keys($lookup_array);
 818          $firstRow = reset($f);
 819          if ((!is_array($lookup_array[$firstRow])) || ($index_number > count($lookup_array))) {
 820              return Functions::REF();
 821          }
 822  
 823          $firstkey = $f[0] - 1;
 824          $returnColumn = $firstkey + $index_number;
 825          $firstColumn = array_shift($f);
 826          $rowNumber = null;
 827          foreach ($lookup_array[$firstColumn] as $rowKey => $rowData) {
 828              // break if we have passed possible keys
 829              $bothNumeric = is_numeric($lookup_value) && is_numeric($rowData);
 830              $bothNotNumeric = !is_numeric($lookup_value) && !is_numeric($rowData);
 831              $lookupLower = StringHelper::strToLower($lookup_value);
 832              $rowDataLower = StringHelper::strToLower($rowData);
 833  
 834              if (
 835                  $not_exact_match && (
 836                  ($bothNumeric && $rowData > $lookup_value) ||
 837                  ($bothNotNumeric && $rowDataLower > $lookupLower)
 838                  )
 839              ) {
 840                  break;
 841              }
 842  
 843              // Remember the last key, but only if datatypes match (as in VLOOKUP)
 844              if ($bothNumeric || $bothNotNumeric) {
 845                  if ($not_exact_match) {
 846                      $rowNumber = $rowKey;
 847  
 848                      continue;
 849                  } elseif (
 850                      $rowDataLower === $lookupLower
 851                      && ($rowNumber === null || $rowKey < $rowNumber)
 852                  ) {
 853                      $rowNumber = $rowKey;
 854                  }
 855              }
 856          }
 857  
 858          if ($rowNumber !== null) {
 859              //  otherwise return the appropriate value
 860              return $lookup_array[$returnColumn][$rowNumber];
 861          }
 862  
 863          return Functions::NA();
 864      }
 865  
 866      /**
 867       * LOOKUP
 868       * The LOOKUP function searches for value either from a one-row or one-column range or from an array.
 869       *
 870       * @param mixed $lookup_value The value that you want to match in lookup_array
 871       * @param mixed $lookup_vector The range of cells being searched
 872       * @param null|mixed $result_vector The column from which the matching value must be returned
 873       *
 874       * @return mixed The value of the found cell
 875       */
 876      public static function LOOKUP($lookup_value, $lookup_vector, $result_vector = null)
 877      {
 878          $lookup_value = Functions::flattenSingleValue($lookup_value);
 879  
 880          if (!is_array($lookup_vector)) {
 881              return Functions::NA();
 882          }
 883          $hasResultVector = isset($result_vector);
 884          $lookupRows = count($lookup_vector);
 885          $l = array_keys($lookup_vector);
 886          $l = array_shift($l);
 887          $lookupColumns = count($lookup_vector[$l]);
 888          // we correctly orient our results
 889          if (($lookupRows === 1 && $lookupColumns > 1) || (!$hasResultVector && $lookupRows === 2 && $lookupColumns !== 2)) {
 890              $lookup_vector = self::TRANSPOSE($lookup_vector);
 891              $lookupRows = count($lookup_vector);
 892              $l = array_keys($lookup_vector);
 893              $lookupColumns = count($lookup_vector[array_shift($l)]);
 894          }
 895  
 896          if ($result_vector === null) {
 897              $result_vector = $lookup_vector;
 898          }
 899          $resultRows = count($result_vector);
 900          $l = array_keys($result_vector);
 901          $l = array_shift($l);
 902          $resultColumns = count($result_vector[$l]);
 903          // we correctly orient our results
 904          if ($resultRows === 1 && $resultColumns > 1) {
 905              $result_vector = self::TRANSPOSE($result_vector);
 906              $resultRows = count($result_vector);
 907              $r = array_keys($result_vector);
 908              $resultColumns = count($result_vector[array_shift($r)]);
 909          }
 910  
 911          if ($lookupRows === 2 && !$hasResultVector) {
 912              $result_vector = array_pop($lookup_vector);
 913              $lookup_vector = array_shift($lookup_vector);
 914          }
 915  
 916          if ($lookupColumns !== 2) {
 917              foreach ($lookup_vector as &$value) {
 918                  if (is_array($value)) {
 919                      $k = array_keys($value);
 920                      $key1 = $key2 = array_shift($k);
 921                      ++$key2;
 922                      $dataValue1 = $value[$key1];
 923                  } else {
 924                      $key1 = 0;
 925                      $key2 = 1;
 926                      $dataValue1 = $value;
 927                  }
 928                  $dataValue2 = array_shift($result_vector);
 929                  if (is_array($dataValue2)) {
 930                      $dataValue2 = array_shift($dataValue2);
 931                  }
 932                  $value = [$key1 => $dataValue1, $key2 => $dataValue2];
 933              }
 934              unset($value);
 935          }
 936  
 937          return self::VLOOKUP($lookup_value, $lookup_vector, 2);
 938      }
 939  
 940      /**
 941       * FORMULATEXT.
 942       *
 943       * @param mixed $cellReference The cell to check
 944       * @param Cell $pCell The current cell (containing this formula)
 945       *
 946       * @return string
 947       */
 948      public static function FORMULATEXT($cellReference = '', ?Cell $pCell = null)
 949      {
 950          if ($pCell === null) {
 951              return Functions::REF();
 952          }
 953  
 954          preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $cellReference, $matches);
 955  
 956          $cellReference = $matches[6] . $matches[7];
 957          $worksheetName = trim($matches[3], "'");
 958          $worksheet = (!empty($worksheetName))
 959              ? $pCell->getWorksheet()->getParent()->getSheetByName($worksheetName)
 960              : $pCell->getWorksheet();
 961  
 962          if (!$worksheet->getCell($cellReference)->isFormula()) {
 963              return Functions::NA();
 964          }
 965  
 966          return $worksheet->getCell($cellReference)->getValue();
 967      }
 968  }