Online sheet  ->  formula  ->  Lookup/reference

Lookup/reference


Formulas Description Examples
AREAS Syntax: AREAS(ref)
This function returns the number of ranges in a reference. Reference is a range of cells.
=AREAS(A1:C3)
CHOOSE Syntax: Choose(position, value1, value2, ...)
Choose a value from a list of values. Position is position number in the list of values to return. It must be a number between 1 and 29.
=CHOOSE(2, "first", "second", "third")
=CHOOSE(6, a1,a2, a3)
COLUMN Syntax: COLUMN(reference)
Column function returns the column number of a cell reference.
=COLUMN()
=COLUMN(B4:B6)
COLUMNS Syntax: COLUMNS(reference)
Columns function returns the number of columns in a cell reference.
=COLUMNS(A4:B6)
HLOOKUP Syntax: HLOOKUP(lookup_value, tabe_array, row_index, [not_exact_match])
Search for value in the top row of tabe_array and returns the value in the same column based on the row_index.
=HLOOKUP("age", A1:D5, 3, TRUE)
HYPERLINK Syntax: HYPERLINK(link_location,friendly_name)
Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet.
=hyperlink("http://www.cubedrive.com")
=hyperlink("Sheet2!A1", "Link to sheet2")
INDEX Syntax: INDEX(reference, row, column)
This function returns either the value or the reference to a value from a table or range.
array is a range of cells or table.
row is the row number in the array to use to return the value.
column is optional. It is the column number in the array to use to return the value.
=INDEX(A1:D5, 4, 1)
LOOKUP Syntax: LOOKUP(lookup_value, lookup_vector, [result_vector])
The LOOKUP function returns a value either from a one-row or one-column range or from an array. The LOOKUP function has two syntax forms: the vector form and the array form.
=LOOKUP(10251, A1:A6, B1:B6)
MATCH Syntax: MATCH(lookup_value, lookup_array, [match_type])
Search for a specified item in a range of cells, and then returns the relative position of that item in the range. The match_type can be 1 (default), 0, -1.
=MATCH(29, A1:D5, 1)
=MATCH("*ge", G498:G501, 0)
OFFSET Syntax: OFFSET(reference, rows, cols, [height], [width])
This function returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.
=OFFSET(D3,3,-2,1,1)
=SUM(OFFSET(D3:F5,3,-2, 3, 3))
ROW Syntax: ROW(reference)
Row function returns the row number of a cell reference.
= ROW ()
= ROW (A1:A2)
ROWS Syntax: ROWS(reference)
Rows function returns the number of rows in a cell reference.
=ROWS(A4:B6)
VLOOKUP Syntax: VLOOKUP(lookup_value, tabe_array, col_index, [not_exact_match])
Search for value in the left-most column of tabe_array and returns the value in the same row based on the col_index.
=VLOOKUP(1,A2:A8,2)
=VLOOKUP(0.1,A1:F10,3,TRUE)