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)
- Press the Enter key to display the 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))
- Press the Enter key to display the result. You can see from the above figure that there are 4 areas.
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.
- Press the Enter key to display the 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).
- Press the Enter key to display the 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)
- Press the Enter key to display the 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.
- Press the Enter key to display the 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”)
- Press the Enter key to display the 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)
- Press the Enter key to display the 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)
- Press the Enter key to display the result.
Conclusion
In this article, we learned about some of the lookup and reference functions in Excel.