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 the remaining lookup and reference functions. If you haven’t already checked out, here are the links to part 1 and part 2.
1. MATCH function
MATCH is an Excel function that locates a lookup value’s location in a row, column, or table. It enables both approximate and exact matching, as well as partial matches using wildcards (*?).
Syntax: MATCH(lookup_value, lookup_array, [match_type]), where lookup_value is the value to match up in lookup_array and match_type is optional, 1 for exact or next smallest match (default), 0 for exact match, and -1 for exact or next largest match. The following example shows the use of MATCH function:
- Select the cell where you want to display the result.
- Type =MATCH(“John”,A1:A10,0), where cells A1:A10 contain the list of names of students.
- Press the Enter key to display the location of occurrence of student named “John”.
Note: Use the new XMATCH function, which is an improved version of MATCH that works in either direction and provides precise matches by default, making it easier to use than its predecessor.
2. OFFSET function
The OFFSET function returns a reference to a range from a cell or range of cells with a specified number of rows and columns. A single cell or a range of cells might be returned as the reference. You have the option of specifying the number of rows and columns to be returned.
Syntax: OFFSET(reference, rows, cols, [height], [width]), where reference is the starting point, rows is the number of rows down the starting reference to offset, cols are the number of columns to the right of the starting reference to offset, height and width to be returned are optional. The following example shows the use of the OFFSET function:
- Select the cell where you want to display the result.
- Type =OFFSET(A1,2,3,2,2), where cell A1 is the starting reference, an array of dimension 2*2 is returned from the 2nd row below and the 3rd column right of the reference.
- Press the Enter key to display the result.
Note:
- The OFFSET returns the #REF! error value if the rows and columns offset reference over the edge of the worksheet.
- If height or width is not specified, it is considered to be the same as the reference’s height and/or width.
3. SORT function
The SORT function in Excel sorts a range or array’s contents in ascending or descending order. One or more columns can be used to sort the values.
Syntax: =SORT(array,[sort_index],[sort_order],[by_col]), where array the range to be sorted, sort_index is optional to indicate the number of columns to sort by, sort_order is optional: 1 for ascending order, -1 for descending order (default is ascending order), and by_col is optional: TRUE for sort by column and FALSE for sort by row (default is FALSE).
The following example shows the use of SORT function:
- Select the cell where you want to display the result.
- Type =SORT(A1:B10,2,-1), where cells A1:B10 contain the table to sort.
- Press the Enter key to display the list of students in descending order of marks obtained.
Note: This function is only available in Office 365.
4. SORTBY function
The SORTBY function in Excel sorts the contents of a range based on the values of another range. The range or array that was used to sort does not have to show in the results.
Syntax: =SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…), where array the range to be sorted, by_array is the basis to sort by, sort_order is optional: 1 for ascending order, -1 for descending order (default is ascending order) and the rest are additional sort order pairs.
The following example shows the use of SORTBY function:
- Select the cell where you want to display the result.
- Type =SORTBY(A1:B10,A1:A10), where cells A1:B10 contain the table to be sorted and cells A1:A10 contain the data to sort by.
- Press the Enter key to display the result. The table is sorted based on the alphabetical order of names.
Note:
- This function is available only in Office 365.
- The SORTBY function is an improved version of the SORT function and is more flexible.
5. TRANSPOSE function
The TRANSPOSE function in Excel flips the orientation of a specified range or array. It transforms a vertical range into a horizontal range and a horizontal range into a vertical range.
Syntax: TRANSPOSE(array), where array is the cell ranges to be transposed. The following example shows the use of TRANSPOSE function:
- Select the cell where you want to display the result.
- Type =TRANSPOSE(A1:G2), where cells A1:G2 contain the table to be transposed.
- Press the Enter key to display the result.
Conclusion
In this article, we learned about some of the lookup and reference functions in Excel.