Workbook Functions: Search

 

CHOOSE(Index, Item list)

Value, from a list of items, indicated by the supplied index number. The index number can also be a cell reference or a formula that returns a value from 1 to 29.

See also: INDEX

 

COLUMN(reference)

Column number of the supplied reference to a cell or range.

See also: COLUMNS, ROW

 

COLUMNS(range)

Number of columns in a range of cells.

See also: COLUMN, ROWS

 

COUNT(value list)

Counts the number of values in a list containing only numbers or numerical values.

See also: AVERAGE, COUNTA, SUM

 

COUNTA(expression list)

Number of non-blank values in the supplied list of expressions, e.g. COUNTA(32, 45, "Earnings", " ") returns 4.

See also: AVERAGE, COUNT, PRODUCT, SUM

 

COUNTIF(range, criteria)

Counts the number of cells within a range that match the given criteria (number, expression or text).

See also: AVERAGE, COUNTA, SUM, SUMIF

 

FIND(search text, text [,start position])

Searches for the specified search text within another text and returns the character position at which the search string first occurs. Start position in text where the search begins (default is 1).

See also: EXACT, LEN, MID, SEARCH

 

HLOOKUP(search item, search range, row index)

Searches the top row of the specified search range for the search item and returns the contents of the cell in the same column as the search item and row given by row index. Contents in first row must be in ascending order (e.g. -1,0,1,...A,...Z,FALSE,TRUE).

See also: INDEX, LOOKUP, MATCH, VLOOKUP

 

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

Returns the contents of a cell in a specified reference range. If there is more than one range, each reference is separated by commas and the whole reference enclosed in parentheses (e.g. (A1:C6, B7:E14)).In this case, range number refers to the range from which data is returned.

See also: CHOOSE, HLOOKUP, LOOKUP, MATCH, VLOOKUP

 

INDIRECT(reference)

Returns the contents of the cell referenced by the specified cell, e.g. INDIRECT(C1) returns the contents of cell D1 if cell C1 contains "D1".

See also: OFFSET

 

LOOKUP(value, lookup range, result range)

Searches for the value in the lookup range and returns the contents of corresponding position in the result range. The sizes of both ranges have to be the same and both ranges can only contain one row or column.

See also: HLOOKUP, INDEX, VLOOKUP

 

MATCH(lookup value, lookup range, comparison)

Compares the lookup value against values in the lookup range and returns the position of the matching value in that range. Comparison represents the type of comparison: 1 matches the largest value that is less than or equal to the lookup value, 0 matches the same value and -1 matches the smallest value that is greater than or equal to the lookup value.

See also: HLOOKUP, INDEX, LOOKUP, VLOOKUP

 

OFFSET(reference, rows, columns [,height] [,width])

Returns the contents of a range that is offset from a starting point, given by the reference, in the spreadsheet, e.g. OFFSET(A1, 2, 4, 3, 2) refers to the range E3:F5.

 

SEARCH(search text, text [,start position])

Locates the position of the first character of a specified text string (search text) within another text string. Start position is the character position where the search begins (default is 1).

See also: FIND, MID, REPLACE, SUBSTITUTE

 

SUBSTITUTE(text, old text, new text [, instance])

Replaces a specified text string (old text), occurring within another text string (text), with a new text string. Instance specifies the occurrence of old text to replace (omission replaces every instance), e.g. SUBSTITUTE ("Shipment 45, Bin 45", "45", "52", 2) returns Shipment 45, Bin 52.

See also: REPLACE, TRIM

 

VLOOKUP(search item, search range, column index)

Compares the information in the first column of the search range to the supplied search item. When a match is found, information located in the same row and supplied column (column index) is returned.

See also: HLOOKUP, INDEX, LOOKUP, MATCH