Lookup

Lookup functions return information about a particular spreadsheet cell or the data it holds.

Function

Description

Choose (Index, Value0, [Value1, ...])

Returns the value of the indexed argument from a variable-length list.

Index	An index (zero based)
Value0, [Value1, ...]	A value, string, cell reference, or cell range.

Column(Cell)

Returns the column number of a spreadsheet Cell.

Column A = 0.0 and Column Z = 25.0

CountError(Cell1, [Cell2, ...])

Returns the number of errors in one or more cells or cell ranges.

Cell1, [Cell2,...]

a cell, a range of cells, or a floating-point value.

ErrFree(Cell or Cell Range)

Converts #ERR to empty cells to suppress error propagation.

Cell Ref

a cell or range of cells

If(Cond, Value1, Value2)

Returns Value1 if Condition is true (nonzero);
returns Value2 if Condition is false (zero).

MaxI(Value0, [Value1, ...])

Returns the index of the maximum Value from a variable-length list of values.

Value0,
[Value1,...]

a cell, a range of cells, or a floating-point value.

MinI(Value0, [Value1, ...])

Returns the index of the minimum Value from a variable-length list of values.

Value0,
[Value1,...]

a cell, a range of cells, or a floating-point value.

NthMaxI(Int, Value0, [Value1, ...])

Returns the index of the Nth-largest Value from a variable-length list of values.

Int	Index (zero-based)
Value0, [Value1,...]	a cell, a range of cells, or a floating-point value.

NthMinI(Int, Value0, [Value1, ...])

Returns the index of the Nth-smallest Value from a variable-length list of values.

Int	Index (zero-based)
Value0, [Value1,...]	a cell, a range of cells, or a floating-point value.

Row(Cell)

Returns the row number a spreadsheet Cell.

Switch (FindCase, Default,
Case0, Value0, [Case1, Value1, ...])

Returns the value corresponding to the first Case that matches the FindCase. If the indexed case is FALSE, then the specified Default value will be returned. If more than one Case in the formula matches the FindCase, then the value corresponding to the first matching Case will be returned.

Switch Example:

• B4 = Switch(B2,-1,"Jan",1,"Feb",2,"Mar",3)

• If cell B2="Jan", the value returned in cell B4 would be 1.

• If cell B2="Feb", the value returned in cell B4 would be 2.

• If cell B2="FEB", the value returned to cell B4 would be the default, -1, because there is no Case in the formula that matches the FindCase.

Note: Switch statement supports string data type for case arguments. String comparisons are case-sensitive.

Note: For function arguments that require a text string, you cannot supply the string directly through If, Switch, or Choose. For example, (If (...)) is invalid. To resolve this issue, place If, Switch, or Choose in a separate cell and create a reference to that cell.