Lookup and Reference

9 Aug 20176 minutes to read

ADDRESS

Address function returns the address of a cell in a worksheet when row and column numbers are given.

Syntax:

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

where:

  • row_num: A numeric value that specifies the row number.

  • column_num: A numeric value that specifies the column number

  • abs_num: This value is optional. A numeric value that specifies the type of reference to return.

  • A1: A logical value that specifies the A1 or R1C1 reference style.

AREAS

The Areas function returns the number of areas that make up the reference.

Syntax:

AREAS(reference)

where:

  • reference is an input argument.

CHOOSE

The Choose function returns the value from a range of values on a specific index.

Syntax:

CHOOSE(index, valuearray)

where:

  • index specifies the index from where you want to retrieve the value.

  • valuearray is the array of value from where you want to retrieve the value.

COLUMN

The Column function returns the column index of the provided column in range.

Syntax:

COLUMN(range)

where:

  • range is to provide the column in range.

COLUMNs

The Columns function returns the number of columns that are contained within the range.

Syntax:

COLUMNS(array)

where:

  • array argument is the range of the number of columns.

FORMULATEXT

FormulaText function returns the formula as a string.

Syntax:

FORMULATEXT (reference)

where:

  • reference is the reference to a cell or range of cells.

Remarks:

  • #N/A occurs when the reference argument is to another workbook that is not opened.

  • #N/A occurs when the reference argument is to a range containing more than one cell.

  • #N/A occurs when the cell used as the reference argument does not contain a formula.

  • #N/A occurs when the formula in the cell is longer than 8192 characters.

  • #N/A occurs when the formula cannot be displayed in the worksheet.

  • #N/A occurs when an external workbook that contains the formula is not opened.

HLOOKUP

Searches for a value in the top row of the array of values and then returns a value in the same column from a row you specify in the array. Use HLookup when your comparison values are located in a row across the top of a table of data and you want to look down a specified number of rows. Use VLookup when your comparison values are located in a column to the left of the data you want to find.

Syntax:

HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)

where:

  • lookup_value is the value to be found in the first row of the table. Lookup_value can be a value, reference, or text string.

  • table_array is a table of information in which data is looked up. Use a reference to a range or a range name.

  • row_index_num is the row number in table_array from which the matching value returns. A row_index_num of 1 returns the first row value in table_array , a row_index_num of 2 returns the second row value in table_array, and so on.

  • range_lookup is a logical value that specifies whether you want HLookup to find an exact match or an approximate match. When True or omitted, an approximate match is returned. In other words, when an exact match is not found, the next largest value that is lesser than the lookup_value is returned. (This requires your lookup values to be sorted.) when False, HLookup finds an exact match

The Hyperlink function creates a hyperlink to a document in a supplied location.

Syntax:

HYPERLINK( linkLocation, friendlyName )

where:

  • linkLocation is the address of the file to link.

  • friendlyName is the text to display in the cell.

INDEX

The Index function returns the exact value from the provided row index and column index from a specific range.

Syntax:

INDEX(range,row,col)

where:

  • range is a string to mention the specific range.

  • row is the integer that indicates the specific row index.

  • col is the integer that indicates the specific column index.

INDIRECT

The Indirect function returns the reference as a string instead of providing the content or range within it.

Syntax:

INDIRECT(content)

where:

  • content is the string that provides the textual representation of the cell.

OFFSET

The Offset function returns a reference to a range that is offset a number of rows and columns from any given range or cell.

Syntax:

OFFSET( range, rows, columns, height, width )

where:

  • range is the starting range from which you want to apply the offset.

  • rows is the number of rows you want to apply as the offset to the range. This can be either a positive or negative number.

  • columns is the number of columns you want to apply as the offset to the range. This can be either a positive or a negative number.

  • height is the number of rows that you want the returned range to be.

  • width is the number of columns that you want the returned range to be.

LOOKUP

Lookup function returns a value either from one-row or one-column range, or from an array. Lookup function has two syntax forms: vector and array.

Vector Form The vector application page of Lookup looks in a one-row or one-column range for a value and then returns a value from the same position in a second one-row or one-column range.

Syntax:

LOOKUP(lookup_value, lookup_vector, result_vector)

where:

  • Array form : The array application page of Lookup looks in the first row or column of an array for the specified value and then returns a value from the same position in the last row or column of the array.

Syntax:

=LOOKUP(lookup_value, array)

Remarks:

  • When the Lookup function cannot find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.

  • When lookup_value is smaller than the smallest value in lookup_vector, Lookup returns the #N/A error value.

TRANSPOSE

The Transpose function copies a horizontal range of cells into a vertical range and vice versa.

Syntax:

TRANSPOSE(array)

where:

  • array argument is a range of spreadsheet cells.

VLOOKUP

Looks up a supplied value in the first column of a table, and returns the corresponding value from another column.

Syntax:

VLOOKUP (value, table, col_index, [range_lookup])

where:

  • value - The value to look for in the first column of a table.

  • table - The table from which to retrieve a value.

  • col_index - The column in the table from which to retrieve a value.

  • range_lookup- TRUE = approximate match (default). FALSE = exact match.