Using formulas to find data
Calc offers numerous methods to find data in a sheet. For example, Edit > Find & Replace moves the display cursor based on simple and advanced searching. Use Data > Filter to limit what is displayed rather than simply moving the cursor. Calc also offers lookup functions used in formulas, for example a formula to look up a student’s grade based on their test score.
Search a block of data using VLOOKUP
Use VLOOKUP to search the first column (columns are vertical) of a block of data and return the value from another column in the same row. For example, search the first column for the name “Fred” and then return the value in the cell two columns to the right. VLOOKUP supports two forms:
VLOOKUP(search_value; search_range; return_column_index) VLOOKUP(search_value; search_range; return_column_index; sort_order)
The first argument, search_value, identifies the value to find. The search value can be text, a number, or a regular expression. For example, Fred searches for the text Fred, 4 searches for the number 4, and F.* is the regular expression for finding something that starts with the letter F.
The second argument, search_range, identifies the cells to search; only the first column is searched. For example, B3:G10 searches the same sheet containing the VLOOKUP formula and Sheet2.B3:G10 searches the range B3:G10 on the sheet named Sheet2.
The return_column_index identifies the column to return; a value of 1 returns the first column in the range. The statement =VLOOKUP("Bob"; A1:G9; 1) finds the first row in A1:G9 containing the text Bob, and returns the value in the first column. The first column is the searched column, so the text Bob is returned. If the column index is 2, then the value in the cell to the right of Bob is returned; column B.
The final column, sort_order, is optional. The default value for sort_order is 1, which specifies that the first column is sorted in ascending order; a value of 0 specifies that the data is not sorted. A non-sorted list is searched by sequentially checking every cell in the first column for an exact match. If an exact match is not found, the text #N/A is returned.
A more efficient search routine is used if the data is sorted in ascending order. If one exact match exists, the returned value is the same as for a non-sorted list; but it is faster. If a match does not exist, the largest value in the column that is less than or equal to the search value is returned. For example, searching for 7 in (3, 5, 10) returns 5 because 7 is between 5 and 10. Searching for 27 returns 10, and searching for 2 returns #N/A because there is no match and no value less than 2.
Use VLOOKUP when:
- The data is arranged in rows and you want to return data from the same row. For example, student names with test and quiz scores to the right of the student’s name.
- Searching the first column of a range of data.
Search a block of data using HLOOKUP
Use HLOOKUP to search the first row (rows are horizontal) of a block of data and return the value from a row in the same column. HLOOKUP supports the same form and arguments as VLOOKUP:
HLOOKUP(search_value; search_range; return_row_index) HLOOKUP(search_value; search_range; return_row_index; sort_order)
Use HLOOKUP when:
- The data is arranged in columns and you want to return data from the same column. For example, student names with test and quiz scores underneath the student’s name.
- Searching the first row of a range of data.
Search a row or column using LOOKUP
LOOKUP is similar to HLOOKUP and VLOOKUP. The search range for the LOOKUP function is a single sorted row or column. LOOKUP has two forms:
LOOKUP(search_value; search_range) LOOKUP(search_value; search_range; return_range)
The search value is the same as HLOOKUP and VLOOKUP. The search range, however, must be a single row or a single column; for example, A7:A12 (values in column A) or C5:Q5 (values in row 5). If the return_range is omitted, the matched value is returned. Using LOOKUP without a return range is the same as using HLOOKUP or VLOOKUP with a column index of 1.
The return range must be a single row or column containing the same number of elements as the search range. If the search value is found in the fourth cell in the search range, then the value in the fourth cell in the return range is returned. The return range can have a different orientation than the search range. In other words, the search range can be a row and the return range may be a column.
Use LOOKUP when:
- The search data is sorted in ascending order.
- The search data is not stored in the same row, column, or orientation as the return data.
Use MATCH to find the index of a value in a range
Use MATCH to search a single row or column and return the position that matches the search value. Use MATCH to find the index of a value in a range. The supported forms for MATCH are as follows:
=MATCH(search_value; search_range) =MATCH(search_value; search_range; search_type)
The search value and search range are the same as for LOOKUP. The final argument, search type, controls how the search is performed. A search type of 1, sorted in ascending order, is the default. A search type of -1 indicates that the list is sorted in descending order. A search type of 0 indicates that the list is not sorted. Regular expressions can only be used on an unsorted list.
Use MATCH when:
- You need an index into the range rather than the value.
- The search data is in descending order and the data is large enough that the data must be searched assuming that it is sorted; because it is faster to sort a sorted list.
Consider the data in Table 1 in the introduction to this chapter. Each student’s information is stored in a single row. Write a formula to return the average grade for Fred. The problem can be restated as Search column A in the range A1:G16 for Fred and return the value in column F (column F is the sixth column). The obvious solution is =VLOOKUP("Fred"; A2:G16; 6). Equally obvious is =LOOKUP("Fred"; A2:A16; F2:F16).
It is common for the first row in a range to contains column headers. All of the search functions check the first row to see if there is a match and then ignore it if it does not contain a match, in case the first row is a header.
What if the column heading Average is known, but not the column containing the average? Find the column containing Average rather than hard coding the value 6. A slight modification using MATCH to find the column yields =VLOOKUP("Fred"; A2:G16; MATCH("Average"; A1:G1; 0)); notice that the heading is not sorted. As an exercise, use HLOOKUP to find Average and then MATCH to find the row containing Fred.
As a final example, write a formula to assign grades based on a student’s average score. Assume that a score less than 51 is an F, less than 61 is an E, less than 71 is a D, less than 81 is a C, less than 91 is a B, and 91 to 100 is an A. Assume that the values in Table 9 are in Sheet2.
Table 9. Associate scores to a grade.
The formula =VLOOKUP(83; $Sheet2.$A$2:$B$7; 2) is an obvious solution. Dollar signs are used so that the formula can be copied and pasted to a different location and it will still reference the same values in Table 9.
|Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY).