1.0.3 • Published 9 years ago

formula-lookup-reference v1.0.3

Weekly downloads
2
License
MIT
Repository
github
Last release
9 years ago

#+OPTIONS: ^:nil toc:2 num:nil tex:nil #+TITLE: Lookup and Reference

  • ADDRESS

    Returns a cell reference given a row and column.

    #+BEGIN_EXAMPLE ADDRESS(1, 1) = "$A$1" #+END_EXAMPLE

    #+BEGIN_EXAMPLE ADDRESS(1, 2, 2) = "B$1" #+END_EXAMPLE

    #+BEGIN_EXAMPLE ADDRESS(3, 2, 3) = "$B3" #+END_EXAMPLE

  • COLUMN

    Returns the column number of a specified cell.

    #+BEGIN_EXAMPLE COLUMN(A1) = "A" #+END_EXAMPLE

    #+BEGIN_EXAMPLE COLUMN("A1") = "A" #+END_EXAMPLE

  • COLUMNS

    Returns the number of columns in a specified array or range.

    #+BEGIN_EXAMPLE COLUMNS({1,2,3,4,5}) = 5 #+END_EXAMPLE

    All columns must be same size.

    #+BEGIN_EXAMPLE COLUMNS({1,2,3,4,5;1,2,3,4,5}) = 5 #+END_EXAMPLE

    When they are not the same size then #VALUE? is returned.

    #+BEGIN_EXAMPLE ISERROR(COLUMNS({1,2,3,4,5;1,2})) #+END_EXAMPLE

  • HLOOKUP

    Lookup a value in a table hortizonally on the first row and retreive a value from the matching column and specified row.

** Syntax

HLOOKUP(lookup_value, lookup_array, index, exactmatch)

** Examples #+BEGIN_EXAMPLE HLOOKUP("C", {{"A","B","C"},{1,2,3}}, 2) = 3 #+END_EXAMPLE

  • INDEX

    Lookup an index in an array or reference.

Array Form * Syntax INDEX(array, row_num, column_num) *** Examples

#+BEGIN_EXAMPLE
  INDEX({{"A","B","C"}}, 1, 1) = "A"
#+END_EXAMPLE

#+BEGIN_EXAMPLE
  INDEX({{"A","B","C"}}, 1, 2) = "B"
#+END_EXAMPLE

#+BEGIN_EXAMPLE
  INDEX({{"A","B","C"}, {"D"}}, 2) = "D"
#+END_EXAMPLE

#+BEGIN_EXAMPLE
  INDEX({"A","B","C";"D"}, 1, 3) = "C"
#+END_EXAMPLE

Referenced Form * Syntax

INDEX(reference, row_offset, [column_offset])
  • INDIRECT

    Returns a cell reference given a string.

    #+BEGIN_EXAMPLE ISCELL(INDIRECT("A1")) #+END_EXAMPLE

  • LOOKUP Vector Form * Syntax LOOKUP(lookup_value, lookup_vector, lookup_array) *** Examples

    #+BEGIN_EXAMPLE
    LOOKUP("C", {"A","B","C"}, {1,2,3}) = 3
    #+END_EXAMPLE

** TODO Array Form

The array form searches the array by row or column depending on the dimensions of the array.

If the array is wider then tall then the search is on the first row; the result being from the matching row and last column.

If the array is taller then wide then the search is on the first column; the result being from the last row and matching column.

*** Syntax

LOOKUP(lookup_value, lookup_array)

*** Examples

Wide Array:
#+BEGIN_EXAMPLE
  LOOKUP("C", {"A","B","C","D","E","F";"foo";1,2,3,4,5,6}) = 3
#+END_EXAMPLE

Tall Array:
#+BEGIN_EXAMPLE
  LOOKUP("C", {"A",1;"B",2;"C",3;"D",4;"E",5;"F",6}) = 3
#+END_EXAMPLE
  • MATCH Returns the column matched by a lookup_value in a lookup_reference. Syntax MATCH(lookup_value, lookup_reference, match_type) Examples #+BEGIN_EXAMPLE =MATCH("b",{"a","b","c"},0) = 2 #+END_EXAMPLE #+BEGIN_EXAMPLE =MATCH("a",{"aa","bb","cc"},0) = NA()
    #+END_EXAMPLE #+BEGIN_EXAMPLE =MATCH("a?",{"aa","bb","cc"},0) = 1 #+END_EXAMPLE #+BEGIN_EXAMPLE =MATCH("?b",{"aa","bb","cc"},0) = 2 #+END_EXAMPLE #+BEGIN_EXAMPLE =MATCH("b~",{"aa","b?","cc"},0) = 2 #+END_EXAMPLE #+BEGIN_EXAMPLE =MATCH("c*c",{"aa","b?","cfoobarc"},0) = 3 #+END_EXAMPLE
  • OFFSET Returns a reference to a cell a given number of rows and columns from a reference. ** Syntax OFFSET(ref, rows, cols, height, width)
  • ROW

    Returns the row number of a specified cell.

    #+BEGIN_EXAMPLE ROW(A1) = 1 #+END_EXAMPLE

    #+BEGIN_EXAMPLE ROW("B10") = 10 #+END_EXAMPLE

  • ROWS

    Returns the number of rows in a specified array or range.

    #+BEGIN_EXAMPLE ROWS({1;2;3;4;5;6}) = 6 #+END_EXAMPLE

  • VLOOKUP

    Lookup a value in a table hortizonally on the first row and retreive a value from the matching row and specified column.

** Syntax

VLOOKUP(lookup_value, lookup_reference, index, exactmatch)

** Examples

#+BEGIN_EXAMPLE VLOOKUP("C", {"A",1;"B",2;"C",3}, 2) = 3 #+END_EXAMPLE