Lookup and Reference functions in Excel

 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


=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


Related post

Leave a Reply