Lookup Functions

The functions that relate to referencing and finding other parts of the spreadsheet are:

 

ADDRESS(row,column,absnum,a1style,sheettext)

This function uses the row and column numbers to create a cell address in text.

argument

description

row

Row number in the cell reference

column

Column number in the cell reference

absnum

[Optional] Type of reference to return; can be any of:

 

Value - Type of Cell Reference Returned

 

1 or omitted - Absolute

 

2 - Absolute row, relative column

 

3 - Relative row, absolute column

 

4 - Relative

a1style

[Optional] Logical value that indicates whether the reference style is A1; if TRUE or omitted, the style is A1; if FALSE, then the style is R1C1

sheettext

[Optional] Name of the sheet to use as an external reference; if omitted, no sheet name is used

 

CHOOSE(index,value1,value2,...)

This function returns a value from a list of values.

argument

description

index

Index of the specified values to return; an integer value between 1 and 255

value1, etc.

Values from which to choose; can have up to 255 values; can be numbers, cell references, cell ranges, defined names, formulas, functions, or text

 

COLUMN(reference)

This function returns the column number of a reference.

 

COLUMNS(array)

This function returns the number of columns in an array.

 

HLOOKUP(value,array,row,approx)

HLOOKUP(value,column,row,approx)

This function searches for a value in the top row and then returns a value in the same column from a specified row.

argument

description

value

Value to be found in the first row

array

Column or range that contains the data to search

row

Row number in the array from which the matching value will be returned

approx

[Optional] Logical value indicating whether to find an approximate match; if omitted, uses TRUE and finds an approximate match

 

INDEX(return,row,col,area)

This function returns a value or the reference to a value from within an array or range.

argument

description

return

Returns a value or a reference of a cell or range of cells

row

Row number in the range

col

Column number in the range

area

[If return is a cell range reference] Area of the range

 

LOOKUP(lookupvalue,lookupvector,resultvector)

LOOKUP(lookupvalue,lookuparray)

This function searches for a value and returns a value from the same location in a second area.

Vector Form

argument

description

lookupvalue

Value for which to search; can be number, text, logical value, or name or reference that refers to a value

lookupvector

Column or cell range that contains one row or one column; can be text, numbers, or a logical value; values need to be in ascending order

resultvector

Column or cell range that contains one row or column; must be the same size as lookupvector

 

 

Array Form

argument

description

lookupvalue

Value for which to search; can be number, text, logical value, or name or reference that refers to a value

lookuparray

Column or range of cells that contains text, numbers, or logical values; values must be ascending order

 

 

OFFSET(reference,rows,cols,height,width)

This function returns a reference to a range. The range is a specified number of rows and columns from a cell or range of cells. The function returns a single cell or a range of cells.

argument

description

reference

The location from which to base the offset

rows

Number of rows to which the upper left cell refers

cols

Number of columns to which the upper left cell refers

height

[Optional] Number of returned rows; if omitted, same as reference

width

[Optional] Number of returned columns; if omitted, same as reference

 

ROW(reference)

This function returns the number of a row from a reference.

 

ROWS(array)

This function returns the number of rows in an array.

 

TRANSPOSE(array)

This function returns a vertical range of cells as a horizontal range or a horizontal range of cells as a vertical range.

 

VLOOKUP(value,array,colindex,approx)

VLOOKUP(value,column,colindex,approx)

This function searches for a value in the leftmost column and returns a value in the same row from a column you specify.

argument

description

value

Value for which to search

array

Column or cell range that contains the data to search

colindex

Column number in the array from which the matching value is returned

approx

[Optional] Logical value indicating whether to find an approximate match; if omitted, uses TRUE and finds an approximate match

 

 

 

See Also:


Formula Functions