Lookup and Reference Functions in Excel [Part 1]

By /
Lookup and Reference Functions in

Lookup and Reference functions help you work with data arrays and come in handy when you need to work across multiple data sets. They can provide information about a range, retrieve the location of a given address or value, or seek up specific values. Here are some of the lookup and reference functions.

Lookup and Reference Functions

Let’s get started with understanding the different types of lookup and reference functions in Excel.

1. ADDRESS function

The ADDRESS function is used to find the address of a cell-based on row and column numbers in a worksheet.

Syntax: ADDRESS(row_num, column_num, [abs_num]), where row_num is the row number, column_num is the column number and abs_num to specify the type of reference. If abs_num is 1 or omitted the reference is absolute, 2: absolute row & relative column, 3: relative row & absolute column, and 4: both relative. The following example shows the use of ADDRESS function:

  • Select the cell where you want to display the result.
  • Type =DATE(12,13)
address formula 1
  • Press the Enter key to display the result.
address result

2. AREAS function

The number of areas in a reference is returned by the AREAS function. A range of consecutive cells or a single cell constitutes an area.

Syntax: AREAS(reference), where reference is the reference to a cell or range of cells. If you want to specify many references in a single argument, you’ll need to use additional parentheses to prevent Microsoft Excel from misinterpreting the comma as a field separator. The following example shows the use of AREAS function:

  • Select the cell where you want to display the result.
  • Type =AREAS((A2:C4,B10,A7,D4:E6))
areas formula
  • Press the Enter key to display the result. You can see from the above figure that there are 4 areas.
areas result

3. CHOOSE function

The CHOOSE function returns a value from a list of values. Its lets you choose from up to 254 options based on the index number.

Syntax: CHOOSE(index_num, value1, [value2], …), where index_num is the value number to be chosen and value1, value2, value etc. are list of values from which one item is to be chosen. The following example shows the use of CHOOSE function:

  • Select the cell where you want to display the result.
  • Type =CHOOSE(3,A1,A2,A3,A4), where cells A1, A2, and A3 contain the choices.
choose formula
  • Press the Enter key to display the result.
choose result

4. COLUMN function

The COLUMN function returns the column number associated with the specified cell reference.

Syntax: COLUMN([reference]), if the reference is not given the current cell’s column number is returned. The following example shows the use of COLUMN function:

  • Select the cell where you want to display the result.
  • Type =COLUMN(B2).
column formula
  • Press the Enter key to display the result.
column result

5. COLUMNS function

The COLUMNS function returns the number of columns in the reference.

Syntax: COLUMNS(array), where array is the reference to a range of cells. The following example shows the use of COLUMNS function:

  • Select the cell where you want to display the result.
  • Type =COLUMNS(H4:K10)
columns formula
  • Press the Enter key to display the result.
columns result

6. FORMULATEXT function

The FORMULATEXT function returns the formula in a reference as a text string.

Syntax: FORMULATEXT(reference), where reference is to a cell or a range of cells. The following example shows the use of FORMULATEXT function:

  • Select the cell where you want to display the result.
  • Type =FORMULATEXT(A2), where cell A2 contains the result of a formula.
formulatext formula
  • Press the Enter key to display the result.
formulatext result

Note:

  • This function is available in Excel 2013 and above versions.
  • FORMULATEXT returns the #N/A error value in the following cases:
    • if the reference cell contains no formula.
    • if the formula in the cell exceeds 8192 characters.
    • if the worksheet is protected, the formula cannot be displayed in the worksheet.
    • if an external workbook containing the formula is not open.

7. HYPERLINK function

The HYPERLINK function generates a shortcut that navigates to a different position in the current workbook or opens a document saved on the Internet. When you click a cell that has a HYPERLINK function, Excel navigates to the specified location or opens the document.

Syntax: HYPERLINK(link_location, [friendly_name]), where link_location is the path and file name for a document in the system or URL in case of a file in the network and friendly_name is the name to be displayed in the cell containing the hyperlink. The following example shows the use of HYPERLINK function:

  • Select the cell where you want to display the result.
  • Type =HYPERLINK(“https://www.microsoft.com/en-in/”,”Microsoft Website”)
hyperlink formula
  • Press the Enter key to display the result.
hyperlink result

8. ROW function

The ROW function returns the row number associated with the specified cell reference.

Syntax: ROW([reference]), if the reference is not given the current cell’s row number is returned. The following example shows the use of ROW function:

  • Select the cell where you want to display the result.
  • Type =ROW(B2)
row formula
  • Press the Enter key to display the result.
row result

9. ROWS function

The ROWS function returns the number of rows in the reference.

Syntax: ROWS(array), where array is the reference to a range of cells. The following example shows the use of ROWS function:

  • Select the cell where you want to display the result.
  • Type =ROWS(H4:K10)
rows formula
  • Press the Enter key to display the result.
rows result

Conclusion

In this article, we learned about some of the lookup and reference functions in Excel.

References