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 in Excel
Let’s get started with the next set of lookup and reference functions in Excel. If you haven’t read through the first part, click the below link.
Check: Lookup and Reference Functions in Excel [Part 1]
1. FILTER function
The Excel FILTER function filters a set of data based on the criteria you specify and retrieves matching entries. It filters an array based on True or False conditions.
Syntax: =FILTER(array,include,[if_empty]), where array is the range to filter, include is the array supplied to be used for criteria matching, and if_empty is the value to be returned when no condition matches the filter criteria.
The following example shows the use of FILTER function:
- Select the cell where you want to display the result.
- Type =FILTER(A2:A11,B2:B11>80,”Not found”), where cells A2:A11 and B2:B11 contain the name of students and marks obtained respectively.
- Press the Enter key to display the names of students who have scored > 80.
Note: This function is available only in Office 365.
2. HLOOKUP function
The HLOOKUP function in Excel locates and retrieves a value from a horizontal table of data. H stands for “horizontal,” and lookup values must appear in the table’s first row, sliding horizontally to the right. It allows both approximate and accurate matching, as well as partial matches using wildcards (*?).
Syntax: HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]), where lookup_value is the value to look for, table_array is the table to look up for a value, row_index is the row number to retrieve data from, and range_lookup is a Boolean value that indicates whether a match is exact or approximate.
The default value is TRUE indicating an approximate match. The following example shows the use of HLOOKUP function:
- Select the cell where you want to display the result.
- Type =HLOOKUP(B2,$F$1:$K$2,2,TRUE), where cells F1:K2 contain the table to look up, and data is retrieved from row 2 of the table.
- Press the Enter key and drag down the fill handle to copy the formula for the entire list to display the grades of students based on their marks.
Note: Use the new XLOOKUP function, which is a better version of HLOOKUP that works in either direction and provides precise matches by default, making it easier to use than its predecessor.
3. INDEX function
The INDEX function in Excel returns the value at a certain location in a range or array. Individual values, as well as full rows and columns, can be retrieved using the INDEX function.
Syntax: INDEX(array, row_num, [column_num]), where the array is a range of cells, row_num is the row position, and col_num is the column position. The following example shows the use of the INDEX function:
- Select the cell where you want to display the result.
- Type =INDEX(A1:C11,9,1), where cells A1:C11 contain the name of students marks obtained and grades.
- Press the Enter key to display the data in the 1st column and 9th row.
4. INDIRECT function
The reference supplied by a text string is returned by the INDIRECT function. The contents of references are displayed immediately after they have been examined. When you wish to update a formula’s reference to a cell without affecting the formula itself, use the INDIRECT function.
Syntax: INDIRECT(ref_text), where ref_text is the reference supplied. The following example shows the use of INDIRECTfunction:
- Select the cell where you want to display the result.
- Type =INDIRECT(A2)
- Press the Enter key to display the result. The function goes to cell A2, from which it reads another cell reference to B2 and returns the value 9 contained in it.
5. LOOKUP function
The Excel LOOKUP function retrieves the comparable value from another one-column or one-row range after doing an approximate match lookup in a one-column or one-row range.
Syntax: LOOKUP(lookup_value, lookup_vector, [result_vector]), where lookup_value is the value to be searched for, lookup_vector is the search range, and result_vector is optional to return a single row or column of results.
The following example shows the use of LOOKUP function:
- Select the cell where you want to display the result.
- Type =LOOKUP(77,B2:B11,A2:A11), where cells A2:A11 and B2:B11 contain the name of students and marks obtained respectively.
- Press the Enter key to display the name of the person who obtained 77 marks. It looks up for value 77 in cell ranges B2:B11, and returns the value from cell ranges A2:A11 corresponding to the same row.
Note:
- VLOOKUP is a much-improved version of LOOKUP available in all recent versions of Excel. It can be used to search a single row or column, or several rows and columns.
- Use XLOOKUP if you’re using Office 365. It’s not only faster but also allows search in any direction, unlike HLOOKUP and VLOOKUP.
Conclusion
In this article, we learned about some of the lookup and reference functions in Excel.