# Lookup and Reference in Windows Forms Calculation Engine

13 Jul 20219 minutes to read

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

Syntax:

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.

• sheet_text: A text value that specifies the name of the worksheet to be used as a external sheet reference.

## 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:

where:

• 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)

• lookup_value searches in the first vector. It can be a number, text, a logical value, or a name or reference that refers to a value.

• lookup_vector denotes the cell range which contains only one row/column. The values in lookup_vector can be text, numbers, or logical values.

• result_vector denotes the cell range that contains only one row or column. The result_vector argument must be the same size as lookup_vector. It has to be the same size.

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.

## MATCH

Finds the relative position of a value in a supplied array

Syntax:

MATCH(lookup_value, lookup_array, [match_type])

where:

• lookup_value denotes the value that you want to match in lookup_array. For example, when you look up someone’s number in a telephone book, you are using the person’s name as the lookup value, but the telephone number is the value you want.

• lookup_array denotes the range of cells being searched.

• match_type denotes the number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.

## ROW

Returns the row number of a supplied range, or of the current cell.

Syntax:

ROW([reference])

where:

• reference denotes the cell or range of cells for which you want the row number.

• If reference is omitted, it is assumed to be the reference of the cell in which the ROW function appears.

• If reference is a range of cells, and if ROW is entered as a vertical array, ROW returns the row numbers of reference as a vertical array.

• Reference cannot refer to multiple areas.

## ROWS

Returns the number of rows in a supplied range.

Syntax:

ROWS(array)

where:

array denotes an array, an array formula, or a reference to a range of cells for which you want the number of rows.

## SHEET

Returns the sheet number of the referenced sheet.

Syntax:

SHEET(value)

where:

value is the name of a sheet or a reference for which you want the sheet number. If value is omitted, SHEET returns the number of the sheet that contains the function.

Remarks:

SHEET includes all worksheets (visible, hidden, or very hidden) in addition to all other sheet types (macro, chart, or dialog sheets).

## SHEETS

Returns the number of sheets in reference

Syntax:

SHEETS(reference)

where:

• reference is a reference for which you want to know the number of sheets it contains. If Reference is omitted, SHEETS returns the number of sheets in the workbook that contains the function.

Remarks:

• SHEETS includes all worksheets (visible, hidden, or very hidden) in addition to all other sheet types (macro, chart, or dialog sheets).

• If reference is not a valid value, SHEETS returns the #REF! error value.

• SHEETS is not available in the Object Model (OM) because the Object Model already includes similar functionality.

• If the value argument is a sheet name that is not valid, SHEET returns the #NA error value. For example =SHEET(“badSheetName”) will return the #NA error value.

• SHEET is not available in the Object Model (OM) because the Object Model already includes similar functionality.

• If the value argument is not a valid value, SHEET returns the #REF! error value. For example, =SHEET(Sheet1!#REF) will return the #REF! error value.

• If the value argument is a sheet name that is not valid, SHEET returns the #NA error value. For example =SHEET(“badSheetName”) will return the #NA error value.

• SHEET is not available in the Object Model (OM) because the Object Model already includes similar functionality.