#+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