Chapter 35: Lookup and Reference Functions in Excel

Introduction

Lookup and reference functions in Excel help you find, reference, and manipulate data across different ranges, tables, and sheets. These functions are crucial for managing and analyzing data efficiently. In this chapter, we’ll cover several important functions: ADDRESS, AREA, CHOOSE, COLUMN, COLUMNS, FORMULATEXT, GETPIVOTDATA, HYPERLINK, INDIRECT, LOOKUP, OFFSET, ROW, ROWS, and TRANSPOSE.

1. ADDRESS Function

• Description: Returns the cell address as a text, given a row and column number.
• Syntax: ADDRESS(row_num, column_num, [abs_num], [a1], [sheet])
• Example: =ADDRESS(2, 3) returns “$C$2”.

2. AREA Function

• Description: Returns the number of cells in a given area or range.
• Syntax: AREA(reference)
• Example: =AREA(A1:B10) returns 20 (the number of cells in the range).

3. CHOOSE Function

• Description: Returns a value from a list of values based on an index number.
• Syntax: CHOOSE(index_num, value1, [value2], …)
• Example: =CHOOSE(2, “Apple”, “Banana”, “Cherry”) returns “Banana”.

4. COLUMN Function

• Description: Returns the column number of a cell reference.
• Syntax: COLUMN([reference])
• Example: =COLUMN(B3) returns 2.

5. COLUMNS Function

• Description: Returns the number of columns in a given reference.
• Syntax: COLUMNS(array)
• Example: =COLUMNS(A1:D1) returns 4.

6. FORMULATEXT Function

• Description: Returns the formula as text from a cell.
• Syntax: FORMULATEXT(reference)
• Example: =FORMULATEXT(B2) displays the formula used in cell B2.

7. GETPIVOTDATA Function

• Description: Retrieves specific data from a PivotTable report.
• Syntax: GETPIVOTDATA(data_field, pivot_table, [field1, item1], …)
• Example: =GETPIVOTDATA(“Sales”, A1, “Region”, “West”) retrieves sales data for the “West” region from the PivotTable starting in cell A1.

8. HYPERLINK Function

• Description: Creates a shortcut or jump that opens a document or web page.
• Syntax: HYPERLINK(link_location, [friendly_name])
• Example: =HYPERLINK(“http://www.example.com”, “Visit Example”) creates a hyperlink with the text “Visit Example”.

9. INDIRECT Function

• Description: Returns the reference specified by a text string.
• Syntax: INDIRECT(ref_text, [a1])
• Example: =INDIRECT(“A1”) returns the value of cell A1.

10. LOOKUP Function

• Description: Looks up a value in a one-row or one-column range and returns a value from the same position in another range.
• Syntax: LOOKUP(lookup_value, lookup_vector, [result_vector])
• Example: =LOOKUP(2, A1:A5, B1:B5) looks for 2 in the range A1:A5 and returns the corresponding value from B1:B5.

11. OFFSET Function

• Description: Returns a reference to a range that is a specified number of rows and columns from a cell or range.
• Syntax: OFFSET(reference, rows, cols, [height], [width])
• Example: =OFFSET(A1, 2, 3, 1, 1) returns the cell D3 (2 rows down and 3 columns to the right of A1).

12. ROW Function

• Description: Returns the row number of a reference.
• Syntax: ROW([reference])
• Example: =ROW(C3) returns 3.

13. ROWS Function

• Description: Returns the number of rows in a given reference.
• Syntax: ROWS(array)
• Example: =ROWS(A1:A10) returns 10.

14. TRANSPOSE Function

• Description: Converts a vertical range of cells to a horizontal range and vice versa.
• Syntax: TRANSPOSE(array)
• Example: =TRANSPOSE(A1:B2) converts a vertical range A1:A2 to a horizontal range.

Practice Assignment

1. ADDRESS and INDIRECT:
   • Use the ADDRESS function to find the cell address of a given row and column. Then use the INDIRECT function to reference that cell and display its value.
2. CHOOSE and COLUMN:
   • Create a list of values and use the CHOOSE function to select values based on an index number. Utilize the COLUMN function to determine the column number of a cell.
3. GETPIVOTDATA and HYPERLINK:
   • Use the GETPIVOTDATA function to retrieve specific data from a PivotTable. Create a hyperlink using the HYPERLINK function to link to a website or document.
4. OFFSET and ROWS:
   • Apply the OFFSET function to return a reference to a range of cells. Use the ROWS function to count the number of rows in the reference range.

Pro Tip: Understanding how to use lookup and reference functions effectively can streamline your data analysis and reporting processes. Combining these functions with other Excel capabilities can significantly enhance your productivity. For in-depth learning and practical applications, Anjni Computer Education offers expert tutorials and resources to help you master these essential functions.