Lookup

ADDRESS

Returns a cell reference as a string.

Sample Usage

ADDRESS(1,2)

ADDRESS(1,2,4,FALSE)

ADDRESS(1,2,,,"Sheet2")

Syntax

ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])

row - The row number of the cell reference
column - The column number (not name) of the cell reference. A is column number 1.
absolute_relative_mode - [ OPTIONAL - 1 by default ] - An indicator of whether the reference is row/column absolute. 1 is row and column absolute (e.g. $A$1), 2 is row absolute and column relative (e.g. A$1), 3 is row relative and column absolute (e.g. $A1), and 4 is row and column relative (e.g. A1).
use_a1_notation - [ OPTIONAL - TRUE by default ] - A boolean indicating whether to use A1 style notation (TRUE) or R1C1 style notation (FALSE).
sheet - [ OPTIONAL - absent by default ] - A string indicating the name of the sheet into which the address points.
Examples

Returns the cell address (reference) as text, according to the specified row and column numbers, using different types of reference.

Notes

When using optional parameters such as sheet, ensure that commas are inserted to indicate which parameter is being set.

CHOOSE

Returns an element from a list of choices based on index.

Sample Usage

CHOOSE(2,"A","B","C")

Syntax

CHOOSE(index, choice1, [choice2, ...])

index - Which choice (of the up to 30 provided) to return.
- If index is zero, negative, or greater than the number of choices provided, the #VALUE! error is returned.
choice1 - A potential value to return. Required. May be a reference to a cell or an individual value.
choice2, ... - Additional values among which to choose.

Examples

Uses an index to return a value from the specified list of up to 30 values.

COLUMN

Returns the column number of a specified cell, with `A=1`.

Sample Usage

COLUMN(A9)

Syntax

COLUMN([cell_reference])

cell_reference - [ OPTIONAL - By default, the cell containing the formula ] - The cell whose column number will be returned. Column A corresponds to 1.
- if cell_reference is a range more than one cell wide and the formula is not used as an array formula, the position of the first column in cell_reference is returned.

Examples

Returns the column number of a cell reference.

COLUMNS

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

Sample Usage

COLUMNS(A9:W62)

COLUMNS({1,2,3,4,5})

Syntax

COLUMNS(range)

range - The range whose column count will be returned

Examples

Returns the number of columns in the given reference.

HLOOKUP

Horizontal lookup. Searches across the first row of a range for a key and returns the value of a specified cell in the column found.

Sample Usage

HLOOKUP(10003, A2:Z6, 2, FALSE)

Syntax

HLOOKUP(search_key, range, index, [is_sorted])

search_key - The value to search for. For example, 42, "Cats", or I24.
range - The range to consider for the search. The first row in the range is searched for the key specified in search_key.
index - The row index of the value to be returned, where the first row in range is numbered 1.
- If index is not between 1 and the number of rows in range, #VALUE! is returned.
is_sorted - [OPTIONAL - TRUE by default] - Indicates whether the row to be searched (the first row of the specified range) is sorted.
- If is_sorted is TRUE or omitted, the nearest match (less than or equal to the search key) is returned. If all values in the search row are greater than the search key, #N/A is returned.
- If is_sorted is set to TRUE or omitted, and the first row of the range is not in sorted order, an incorrect value might be returned.
- If is_sorted is FALSE, only an exact match is returned. If there are multiple matching values, the content of the cell corresponding to the first value found is returned, and #N/A is returned if no such value is found.

Notes

When searching for numeric or date values, make sure that the first row in the range is not sorted by text values. For example, correctly sorted numbers should appear as (1, 2, 10, 100) rather than (1, 10, 100, 2) as they would be if they were sorted as strings. Using an incorrect sort type may cause incorrect values to be returned.
Search keys based on regular expressions or wildcard patterns are NOT supported. Use QUERY instead.
HLOOKUP has much better performance with sorted ranges and is_sorted set to TRUE. Consider sorting the row being searched.

Examples

In this example, HLOOKUP searches across the first row for a student ID and returns the corresponding grade.

In this example, HLOOKUP searches across the first row for the income using approximate match (is_sorted is set to TRUE) and returns the corresponding tax rate.

In this example, HLOOKUP returns the first value found when there are multiple matches for the search_key.

HLOOKUP

Horizontal lookup. Searches across the first row of a range for a key and returns the value of a specified cell in the column found.

Sample Usage

HLOOKUP(10003, A2:Z6, 2, FALSE)

Syntax

HLOOKUP(search_key, range, index, [is_sorted])

search_key - The value to search for. For example, 42, "Cats", or I24.
range - The range to consider for the search. The first row in the range is searched for the key specified in search_key.
index - The row index of the value to be returned, where the first row in range is numbered 1.
- If index is not between 1 and the number of rows in range, #VALUE! is returned.
is_sorted - [OPTIONAL - TRUE by default] - Indicates whether the row to be searched (the first row of the specified range) is sorted.
- If is_sorted is TRUE or omitted, the nearest match (less than or equal to the search key) is returned. If all values in the search row are greater than the search key, #N/A is returned.
- If is_sorted is set to TRUE or omitted, and the first row of the range is not in sorted order, an incorrect value might be returned.
- If is_sorted is FALSE, only an exact match is returned. If there are multiple matching values, the content of the cell corresponding to the first value found is returned, and #N/A is returned if no such value is found.

Notes

When searching for numeric or date values, make sure that the first row in the range is not sorted by text values. For example, correctly sorted numbers should appear as (1, 2, 10, 100) rather than (1, 10, 100, 2) as they would be if they were sorted as strings. Using an incorrect sort type may cause incorrect values to be returned.
Search keys based on regular expressions or wildcard patterns are NOT supported. Use QUERY instead.
HLOOKUP has much better performance with sorted ranges and is_sorted set to TRUE. Consider sorting the row being searched.

Examples

In this example, HLOOKUP searches across the first row for a student ID and returns the corresponding grade.

In this example, HLOOKUP searches across the first row for the income using approximate match (is_sorted is set to TRUE) and returns the corresponding tax rate.

In this example, HLOOKUP returns the first value found when there are multiple matches for the search_key.

HYPERLINK

Creates a hyperlink inside a cell.

Sample Usage

HYPERLINK("http://www.google.com/","Google")

Syntax

HYPERLINK(url, [link_label])

url - The full URL of the link location enclosed in quotation marks, or a reference to a cell containing such a URL.
- Only certain link types are allowed. http://, https://, mailto:, aim:, ftp://, gopher://, telnet://, and news:// are permitted; others are explicitly forbidden. If another protocol is specified, link_label will be displayed in the cell, but will not be hyperlinked.
- If no protocol is specified, http:// is assumed, and is prepended to url.
link_label - [ OPTIONAL - url by default ] - The text to display in the cell as the link, enclosed in quotation marks, or a reference to a cell containing such a label.
- If link_label is a reference to an empty cell, url will be displayed as a link if valid, or as plain text otherwise.
- If link_label is the empty string literal (""), the cell will appear empty, but the link is still accessible by clicking or moving to the cell.

Notes

Failure to enclose url (or link_label, if provided) in quotation marks will cause an error.
Google Sheets automatically converts most valid URL types when typed into a cell without the need to use this function.

Examples

Creates a hyperlink inside the cell for specified cell_text.

INDEX

Returns the content of a cell, specified by row and column offset.

Sample Usage

INDEX(A1:C20, 5, 1)

Syntax

INDEX(reference, [row], [column])

reference - The array of cells to be offset into.
row - [OPTIONAL - 0 by default] - The number of offset rows.
column - [OPTIONAL - 0 by default] - The number of offset columns.

Examples

Returns the content of a cell, specified by row and column number or an optional range name.

INDIRECT

Returns a cell reference specified by a string.

Sample Usage

INDIRECT("Sheet2!"&B10)

INDIRECT("A2")

INDIRECT("R2C3", FALSE)

Syntax

INDIRECT(cell_reference_as_string, [is_A1_notation])

cell_reference_as_string - A cell reference, written as a string with surrounding quotation marks.
is_A1_notation - [OPTIONAL - TRUE by default] - Indicates if the cell reference is in A1 notation or R1C1 notation.

Examples

Student Info data as a separate sheet in the spreadsheet.

Returns the contents of the reference which can be a cell or an area.

MATCH

Returns the relative position of an item in a range that matches a specified value.

Sample Usage

MATCH("Sunday",A2:A9,0)

MATCH(DATE(2012,1,1),A2:F2)

Syntax

MATCH(search_key, range, search_type)

search_key - The value to search for. For example, 42, "Cats", or I24.
range - The one-dimensional array to be searched.
- If a range with both height and width greater than 1 is used, MATCH will return #N/A!.
search_type - [ OPTIONAL - 1 by default ] - The manner in which to search.
- 1, the default, causes MATCH to assume that the range is sorted in ascending order and return the largest value less than or equal to search_key.
- 0 indicates exact match, and is required in situations where range is not sorted.
- -1 causes MATCH to assume that the range is sorted in descending order and return the smallest value greater than or equal to search_key.

Notes

MATCH returns the position in an array or range of a matched value rather than the value itself. To return the value itself or another value corresponding to the row or column the match is found in, use INDEX, HLOOKUP, or VLOOKUP.

Examples

Returns the relative position of an item in an array that matches a specified value based on differnet search_type.

OFFSET

Returns a range reference shifted a specified number of rows and columns from a starting cell reference.

Sample Usage

OFFSET(A2,3,4,2,2)

OFFSET(A2,1,1)

Syntax

OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])

cell_reference - The starting point from which to count the offset rows and columns.
offset_rows - The number of rows to shift by.
- offset_rows must be an integer, but may be negative. If a decimal value is provided, the decimal part will be truncated.
offset_columns - The number of columns to shift by.
- offset_columns must be an integer, but may be negative. If a decimal value is provided, the decimal part will be truncated.
height - [ OPTIONAL ] - The height of the range to return starting at the offset target.
width - [ OPTIONAL ] - The width of the range to return starting at the offset target.

Notes

If offset_rows or offset_columns is negative, it is possible for the offset target to to be outside the upper or left edge of the spreadsheet. If this occurs, the #REF! error will be returned.
If OFFSET is used as an array formula, it is possible for the value returned by the array formula to overwrite part of the offset target, causing a circular reference. If this occurs, the #REF! error will be returned.

Examples

Returns the value of a cell offset by a certain number of rows and columns from a given reference point.

ROW

Returns the row number of a specified cell.

Sample Usage

ROW(A9)

Syntax

ROW([cell_reference])

cell_reference - [ OPTIONAL - The cell in which the formula is entered by default ] - The cell whose row number will be returned.
- if cell_reference is a range more than one cell wide and the formula is not used as an array formula, only the numeric value of the first row in cell_reference is returned.

Examples

Returns the row number of a cell reference.

ROWS

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

Sample Usage

ROWS(A9:A62)

ROWS({1;2;3;4;5})

Syntax

ROWS(range)

range - The range whose row count will be returned

Examples

Returns the number of rows in a reference or array.

VLOOKUP

Vertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found.

Sample Usage

VLOOKUP(10003, A2:B26, 2, FALSE)

Syntax

VLOOKUP(search_key, range, index, [is_sorted])

search_key - The value to search for. For example, 42, "Cats", or I24.
range - The range to consider for the search. The first column in the range is searched for the key specified in search_key.
index - The column index of the value to be returned, where the first column in range is numbered 1.
- If index is not between 1 and the number of columns in range, #VALUE! is returned.
is_sorted - [TRUE by default] - Indicates whether the column to be searched (the first column of the specified range) is sorted. FALSE is recommended in most cases.
- It’s recommended to set is_sorted to FALSE. If set to FALSE, an exact match is returned. If there are multiple matching values, the content of the cell corresponding to the first value found is returned, and #N/A is returned if no such value is found.
- If is_sorted is TRUE or omitted, the nearest match (less than or equal to the search key) is returned. If all values in the search column are greater than the search key, #N/A is returned.

Notes

If is_sorted is set to TRUE or omitted, and the first column of the range is not in sorted order, an incorrect value might be returned. If VLOOKUP doesn’t appear to be giving correct results, check that the last argument is set to FALSE. If the data is sorted and you need to optimize for performance, set it to TRUE. In most cases it should be set to FALSE.
When searching for numeric or date values, make sure that the first column in the range is not sorted by text values. For example, correctly sorted numbers should appear as (1, 2, 10, 100) rather than (1, 10, 100, 2) as they would be if they were sorted as strings. Using an incorrect sort type may cause incorrect values to be returned.
Search keys based on regular expressions are NOT supported. Use QUERY instead.
VLOOKUP has much better performance with sorted ranges and is_sorted set to TRUE. Consider sorting the column being searched.
You can also find matches using pattern strings that include wildcards. The question mark (?) and asterisk (*) are the wildcards for search_key, with the question mark standing in for a single character and the asterisk standing in for any series of characters. If you need to match an actual question mark or asterisk, add a tilde (~) before the character and add an extra tilde if you're looking for something with an actual tilde in it.

Examples

In this example, VLOOKUP searches down the first column for a student ID and returns the corresponding grade

In this example, VLOOKUP searches down the first column for the income using approximate match (is_sorted is set to TRUE) and returns the corresponding tax rate

VLOOKUP returns the first value found when there are multiple matches for the search_key

Lookup

ADDRESS

Sample Usage

Syntax

Examples

Notes

See Also

CHOOSE

Sample Usage

Syntax

See Also

Examples

COLUMN

Sample Usage

Syntax

See Also

Examples

COLUMNS

Sample Usage

Syntax

See Also

Examples

HLOOKUP

Sample Usage

Syntax

Notes

See Also

Examples

HLOOKUP

Sample Usage

Syntax

Notes

See Also

Examples

HYPERLINK

Sample Usage

Syntax

Notes

Examples

INDEX

Sample Usage

Syntax

See Also

Examples

INDIRECT

Sample Usage

Syntax

Examples

MATCH

Sample Usage

Syntax

Notes

See Also

Examples

OFFSET

Sample Usage

Syntax

Notes

Examples

ROW

Sample Usage

Syntax

See Also

Examples

ROWS

Sample Usage

Syntax

See Also

Examples

VLOOKUP

Sample Usage

Syntax

Notes

See Also

Examples

Comments

Post a Comment