# Lookup and Reference functions in Excel

**Lookup and Reference functions in Excel**

There are many useful lookup and reference functions in Excel that help users to get row/column information and also work with arrays of data. You could find these functions if you go to FormulasLookup & Reference as shown below.

If you wish to get the cell reference in the form of string, you can use ADDRESS function. The syntax is ADDRESS (*row_num, column_num, abs_num, a1, sheet_text*). Here, *row_num* and *column_num* specify the row number and column number respectively of the cell to be referenced. *abs_num* is an optional argument to specify whether the cell should be absolute or relative referenced. If the value of *abs_num *is 1, then it denotes absolute row and absolute column reference; 2 means absolute row and relative column reference; 3 means relative row and absolute column reference and 4 means relative row and relative column reference. The default value of *abs_num *is 1. *a1 *is an optional argument that specifies the style reference: whether it is A1 style or R1C1 style. If the value is true, then the cell reference is A1 style and if the value is false, then it is R1C1 style. *sheet_text* is an optional argument that specifies the name of the sheet. For example, if you wish to make the cell B7 absolute referenced, then you need to enter the formula =ADDRESS(7,2) and you will get the result $B$7. If you enter the formula =ADDRESS(2,3,3), then the result would be $C2 because abs_num=3 means relative row and absolute column reference. If you enter the formula =ADDRESS(ROW(),COLUMN()) in any of the cell, you will get the absolute reference of the current cell where you entered the formula.

The function ROW returns the row number of the current cell or the first row number if a specific cell or array of cells is provided. Similarly, the function COLUMN returns the column number of the current cell or the first column number if a specific cell or array of cells is provided. The syntax of ROW function is ROW (*reference*) and the syntax of COLUMN function is COLUMN (*reference*). Here the argument *reference* is optional. If you enter the following formulas clicking in cell C6, the results would be as follows.

=ROW() 6

=COLUMN() 3

=ROW(D12) 12

=COLUMN(D12) 4

=ROW(G17:K23) 17

=COLUMN(G17:K23) 7

You can use CHOOSE function to choose a value from the provided list with a given index number and MATCH function to find the relative position of a value from a given list. The syntax of CHOOSE function is CHOOSE (*index_num, value1, value2*,….). Here *index_num* is the index of the item to be chosen and *value1, value2* etc are values that you wish to choose from. This function returns #VALUE! error if *index_num* is less than 1 or greater than supplied number of values. The formulas you can try and the corresponding results are shown below.

=CHOOSE( 3, “Aaron”, “Sarah”, “Hannah”, “Clint”, “Alex” )Hannah

=CHOOSE( 1, “Aaron”, “Sarah”, “Hannah”, “Clint”, “Alex” ) Aaron

=CHOOSE( 0, “Aaron”, “Sarah”, “Hannah”, “Clint”, “Alex” ) #VALUE!

=CHOOSE( 6, “Aaron”, “Sarah”, “Hannah”, “Clint”, “Alex” ) #VALUE!

The syntax of MATCH function is MATCH (*lookup_value, lookup_array, match_type*). Here *lookup_value* is the value that you want to look up and *lookup_array* is the array that is to be searched for *lookup_value. match_type *is an optional parameter specifying whether to go for exact match or closest match. You can include wildcard characters while searching. Suppose you have an array as shown below.

The formulas you can try and the corresponding results are shown below.

=MATCH(“Sarah”,A2:A6,0) 2

=MATCH(“*x”,A2:A6,0) 5

=MATCH(“?d”,A2:A6,0) #N/A (means no match found)

If you want to create a hyperlink to a document available on the hard drive, network server or on the Internet, then you can use HYPERLINK function. The syntax of HYPERLINK function is HYPERLINK(*link_location, friendly_name*). *link_location* specifies the location of the file to link to and *friendly_name* specifies the text that is displayed in the Excel cell. In case of *link_location* , you can go for either absolute reference by specifying the entire path of the location or relative reference by specifying the location relative to the current directory. If *friendly_name *is omitted, then *link_location* text is displayed in the cell. Suppose you store the student details in an Excel file, StudentDetails.xls, with separate hyperlinks for the result details of each student. You need to enter the formulas =HYPERLINK(“C:\Students\Aaron-Marks.pdf”,”Aaron’s Marks”) in cell C2, =HYPERLINK(“C:\Students\Sarah-Marks.pdf”,”Sarah’s Marks”) in cell C3, =HYPERLINK(“Hannah-Marks.pdf”,”Hannah’s Marks”) in cell C4, =HYPERLINK(“C:\Students\Clint-Marks.pdf”,”Clint’s Marks”) in cell C5 and =HYPERLINK(“C:\Students\Alex-Marks.pdf”,”Alex’s Marks”) in cell C6. Then, you will get an Excel as follows.

You should have result details of different students in C:\Students folder. Here, it is assumed that the current Excel file StudentDetails.xls is stored in the same folder C:\Students. Thus the formula in cell C4 uses relative reference. As the second argument is omitted in the formula in C6, the location value is displayed as the text in the cell.

Read More about Excel here