This chapter discusses various techniques that you can use to look up a value in a range of data. Excel has three functions (LOOKUP
, VLOOKUP
, and HLOOKUP
) designed for this task, but you may find that these functions don’t quite cut it.
This chapter provides many lookup examples, including alternative techniques that go well beyond the Excel program’s normal lookup capabilities.
A lookup formula essentially returns a value from a table by looking up another related value. A common telephone directory provides a good analogy. If you want to find a person’s telephone number, you first locate the name (look it up) and then retrieve the corresponding number.
I use the term table to describe a rectangular range of data. The range does not necessarily need to be an “official” table, as created by Excel’s Insert Tables Table command.
Figure 15.1 shows a simple worksheet that uses several lookup formulas. This worksheet contains a table of employee data, beginning in row 7. This range is named EmpData. When you enter a last name into cell C2, lookup formulas in D2:G2 retrieve the matching information from the table. The following lookup formulas use the VLOOKUP
function:
D2 |
|
E2 |
|
F2 |
|
G2 |
|
This particular example uses four formulas to return information from the EmpData range. In many cases, you want only a single value from the table, so use only one formula.
Several Excel functions are useful when writing formulas to look up information in a table. Table 15.1 lists and describes these functions.
Table 15.1. Functions Used in Lookup Formulas
Function | Description |
---|---|
| Returns a specific value from a list of values (up to 29) supplied as arguments. |
| Horizontal lookup. Searches for a value in the top row of a table and returns a value in the same column from a row you specify in the table. |
| Returns one value if a condition you specify is TRUE, and returns another value if the condition is FALSE. |
| If the first argument returns an error, the second argument is evaluated and returned. |
| Returns a value (or the reference to a value) from within a table or range. |
| Returns a value either from a one-row or one-column range. Another form of the LOOKUP function works like |
| Returns the relative position of an item in a range that matches a specified value. |
| Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. |
| Vertical lookup. Searches for a value in the first column of a table and returns a value in the same row from a column you specify in the table. |
[*] Available in Excel 2007 only. |
The examples in this chapter use the functions listed in Table 15.1.
You can use the Excel basic lookup functions to search a column or row for a lookup value to return another value as a result. Excel provides three basic lookup functions: HLOOKUP
, VLOOKUP
, and LOOKUP
. In addition, the MATCH
and INDEX
functions are often used together to return a cell or relative cell reference for a lookup value.
The VLOOKUP
function looks up the value in the first column of the lookup table and returns the corresponding value in a specified table column. The lookup table is arranged vertically (which explains the V in the function’s name). The syntax for the VLOOKUP
function is
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
The VLOOKUP
function’s arguments are as follows:
lookup_value: The value to be looked up in the first column of the lookup table.
table_array: The range that contains the lookup table.
col_index_num: The column number within the table from which the matching value is returned.
range_lookup: Optional. If TRUE or omitted, an approximate match is returned. (If an exact match is not found, the next largest value that is less than lookup_value is returned.) If FALSE, VLOOKUP
will search for an exact match. If VLOOKUP
can’t find an exact match, the function returns #N/A
.
If the range_lookup argument is TRUE or omitted, the first column of the lookup table must be in ascending order. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP
returns #N/A
. If the range_lookup argument is FALSE, the first column of the lookup table need not be in ascending order. If an exact match is not found, the function returns #N/A
.
A very common use for a lookup formula involves an income tax rate schedule (see Figure 15.2). The tax rate schedule shows the income tax rates for various income levels. The following formula (in cell B3) returns the tax rate for the income in cell B2:
=VLOOKUP(B2,D2:F7,3)
The examples in this section are available on the companion CD-ROM. They’re contained in a file named basic lookup examples.xlsx.
The lookup table resides in a range that consists of three columns (D2:F7). Because the last argument for the VLOOKUP
function is 3, the formula returns the corresponding value in the third column of the lookup table.
Note that an exact match is not required. If an exact match is not found in the first column of the lookup table, the VLOOKUP
function uses the next largest value that is less than the lookup value. In other words, the function uses the row in which the value you want to look up is greater than or equal to the row value but less than the value in the next row. In the case of a tax table, this is exactly what you want to happen.
The HLOOKUP
function works just like the VLOOKUP
function except that the lookup table is arranged horizontally instead of vertically. The HLOOKUP
function looks up the value in the first row of the lookup table and returns the corresponding value in a specified table row.
The syntax for the HLOOKUP
function is
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
The HLOOKUP
function’s arguments are as follows
lookup_value: The value to be looked up in the first row of the lookup table.
table_array: The range that contains the lookup table.
row_index_num: The row number within the table from which the matching value is returned.
range_lookup: Optional. If TRUE or omitted, an approximate match is returned. (If an exact match is not found, the next largest value less than lookup_value is returned.) If FALSE, VLOOKUP
will search for an exact match. If VLOOKUP
can’t find an exact match, the function returns #N/A
.
Figure 15.3 shows the tax rate example with a horizontal lookup table (in the range E1:J3). The formula in cell B3 is
=HLOOKUP(B2,E1:J3,3)
The LOOKUP
function has the following syntax:
LOOKUP(lookup_value,lookup_vector,result_vector)
The function’s arguments are as follows:
lookup_value: The value to be looked up in the lookup_vector.
lookup_vector: A single-column or single-row range that contains the values to be looked up. These values must be in ascending order.
result_vector: The single-column or single-row range that contains the values to be returned. It must be the same size as the lookup_vector.
The LOOKUP
function looks in a one-row or one-column range (lookup_vector) for a value (lookup_value) and returns a value from the same position in a second one-row or one-column range (result_vector).
Values in the lookup_vector must be in ascending order. If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP returns #N/A
.
Figure 15.4 shows the tax table again. This time, the formula in cell B3 uses the LOOKUP
function to return the corresponding tax rate. The formula in cell B3 is
=LOOKUP(B2,D2:D7,F2:F7)
If the values in the first column are not arranged in ascending order, the LOOKUP
function may return an incorrect value.
Note that LOOKUP
(as opposed to VLOOKUP
) requires two range references (a range to be looked in, and a range that contains result values). VLOOKUP
, on the other hand, uses a single range for the lookup table, and the third argument determines which column to use for the result. This argument, of course, can consist of a cell reference.
The MATCH
and INDEX
functions are often used together to perform lookups. The MATCH
function returns the relative position of a cell in a range that matches a specified value. The syntax for MATCH
is
MATCH(lookup_value,lookup_array,match_type)
The MATCH
function’s arguments are as follows:
lookup_value: The value you want to match in lookup_array. If match_type is 0 and the lookup_value is text, this argument can include wildcard characters “*” and “?”
lookup_array: The range being searched.
match_type: An integer (–1, 0, or 1) that specifies how the match is determined.
If match_type is 1, MATCH
finds the largest value less than or equal to lookup_value. (lookup_array must be in ascending order.) If match_type is 0, MATCH
finds the first value exactly equal to lookup_value. If match_type is –1, MATCH
finds the smallest value greater than or equal to lookup_value. (lookup_array must be in descending order.) If you omit the match_type argument, this argument is assumed to be 1.
The INDEX
function returns a cell from a range. The syntax for the INDEX
function is
INDEX(array,row_num,column_num)
The INDEX
function’s arguments are as follows:
array: A range
row_num: A row number within array
col_num: A column number within array
If array contains only one row or column, the corresponding row_num or column_num argument is optional.
Figure 15.5 shows a worksheet with dates, day names, and amounts in columns D, E, and F. When you enter a date in cell B1, the following formula (in cell B2) searches the dates in column D and returns the corresponding amount from column F. The formula in cell B2 is
=INDEX(F2:F21,MATCH(B1,D2:D21,0))
To understand how this formula works, start with the MATCH
function. This function searches the range D2:D21 for the date in cell B1. It returns the relative row number where the date is found. This value is then used as the second argument for the INDEX
function. The result is the corresponding value in F2:F21.
You can use additional types of lookup formulas to perform more specialized lookups. For example, you can look up an exact value, search in another column besides the first in a lookup table, perform a case-sensitive lookup, return a value from among multiple lookup tables, and perform other specialized and complex lookups.
The examples in this section are available on the companion CD-ROM. The file is named specialized lookup examples.xlsx.
As demonstrated in the previous examples, VLOOKUP
and HLOOKUP
don’t necessarily require an exact match between the value to be looked up and the values in the lookup table. An example is looking up a tax rate in a tax table. In some cases, you may require a perfect match. For example, when looking up an employee number, you would probably require a perfect match for the number.
To look up an exact value only, use the VLOOKUP
(or HLOOKUP
) function with the optional fourth argument set to FALSE.
Figure 15.6 shows a worksheet with a lookup table that contains employee numbers (column C) and employee names (column D). The lookup table is named EmpList. The formula in cell B2, which follows, looks up the employee number entered in cell B1 and returns the corresponding employee name:
=VLOOKUP(B1,EmpList,2,FALSE)
Because the last argument for the VLOOKUP
function is FALSE, the function returns a value only if an exact match is found. If the value is not found, the formula returns #N/A
. This result, of course, is exactly what you want to happen because returning an approximate match for an employee number makes no sense. Also, notice that the employee numbers in column C are not in ascending order. If the last argument for VLOOKUP
is FALSE, the values need not be in ascending order.
If you prefer to see something other than #N/A
when the employee number is not found, you can use the IFERROR
function to test for the error result and substitute a different string. The following formula displays the text Not Found
rather than #N/A
:
=IFERROR(VLOOKUP(B1,EmpList,2,FALSE),"Not Found")
IFERROR
is new to Excel 2007. For compatibility with previous versions, use the following formula:
=IF(ISNA(VLOOKUP(B1,EmpList,2,FALSE)),"Not Found", VLOOKUP(B1,EmpList,2,FALSE))
The VLOOKUP
function always looks up a value in the first column of the lookup range. But what if you want to look up a value in a column other than the first column? It would be helpful if you could supply a negative value for the third argument for VLOOKUP
—but Excel doesn’t allow it.
Figure 15.7 illustrates the problem. Suppose that you want to look up the batting average (column B, in a range named Averages) of a player in column C (in a range named Players). The player you want data for appears in a cell named LookupValue. The VLOOKUP
function won’t work because the data isn’t arranged correctly. One option is to rearrange your data, but sometimes that’s not possible.
One solution is to use the LOOKUP
function, which requires two range arguments. The following formula (in cell F3) returns the batting average from column B of the player name contained in the cell named LookupValue:
=LOOKUP(LookupValue,Players,Averages)
Using the VLOOKUP
function requires that the lookup range (in this case, the Players range) is in ascending order. In addition to this limitation, the formula suffers from a slight problem: If you enter a nonexistent player (in other words, the LookupValue cell contains a value not found in the Players range), the formula returns an erroneous result.
A better solution uses the INDEX
and MATCH
functions. The formula that follows works just like the previous one except that it returns #N/A
if the player is not found. Another advantage is that the player names need not be sorted.
=INDEX(Averages,MATCH(LookupValue,Players,0))
The Excel lookup functions (LOOKUP
, VLOOKUP
, and HLOOKUP
) are not case sensitive. For example, if you write a lookup formula to look up the text budget, the formula considers any of the following a match: BUDGET, Budget, or BuDgEt.
Figure 15.8 shows a simple example. Range D2:D7 is named Range1, and range E2:E7 is named Range2. The word to be looked up appears in cell B1 (named Value).
The array formula that follows is in cell B2. This formula does a case-sensitive lookup in Range1 and returns the corresponding value in Range2.
{=INDEX(Range2,MATCH(TRUE,EXACT(Value,Range1),0))}
The formula looks up the word DOG (uppercase) and returns 300. The following standard LOOKUP
formula (which is not case sensitive) returns 400:
=LOOKUP(Value,Range1,Range2)
You can, of course, have any number of lookup tables in a worksheet. In some situations, your formula may need to decide which lookup table to use. Figure 15.9 shows an example.
This workbook calculates sales commission and contains two lookup tables: G3:H9 (named CommTable1) and J3:K8 (named CommTable2). The commission rate for a particular sales representative depends on two factors: the sales rep’s years of service (column B) and the amount sold (column C). Column D contains formulas that look up the commission rate from the appropriate table. For example, the formula in cell D2 is
=VLOOKUP(C2,IF(B2<3,CommTable1,CommTable2),2)
The second argument for the VLOOKUP
function consists of an IF
formula that uses the value in column B to determine which lookup table to use.
The formula in column E simply multiplies the sales amount in column C by the commission rate in column D. The formula in cell E2, for example, is
=C2*D2
A common use of a lookup table is to assign letter grades for test scores. Figure 15.10 shows a worksheet with student test scores. The range E2:F6 (named GradeList) displays a lookup table used to assign a letter grade to a test score.
Column C contains formulas that use the VLOOKUP
function and the lookup table to assign a grade based on the score in column B. The formula in cell C2, for example, is
=VLOOKUP(B2,GradeList,2)
When the lookup table is small (as in the example shown earlier in Figure 15.10), you can use a literal array in place of the lookup table. The formula that follows, for example, returns a letter grade without using a lookup table. Rather, the information in the lookup table is hard-coded into an array. See Chapter 17 for more information about arrays.
=VLOOKUP(B2,{0,"F";40,"D";70,"C";80,"B";90,"A"},2)
Another approach, which uses a more legible formula, is to use the LOOKUP
function with two array arguments:
=LOOKUP(B2,{0,40,70,80,90},{"F","D","C","B","A"})
A student’s grade-point average (GPA) is a numerical measure of the average grade received for classes taken. This discussion assumes a letter grade system, in which each letter grade is assigned a numeric value (A=4, B=3, C=2, D=1, and F=0). The GPA comprises an average of the numeric grade values weighted by the credit hours of the course. A one-hour course, for example, receives less weight than a three-hour course. The GPA ranges from 0 (all Fs) to 4.00 (all As).
Figure 15.11 shows a worksheet with information for a student. This student took five courses, for a total of 13 credit hours. Range B2:B6 is named CreditHours. The grades for each course appear in column C. (Range C2:C6 is named Grades.) Column D uses a lookup formula to calculate the grade value for each course. The lookup formula in cell D2, for example, follows. This formula uses the lookup table in G2:H6 (named GradeTable).
=VLOOKUP(C2,GradeTable,2,FALSE)
Formulas in column E calculate the weighted values. The formula in cell E2 is
=D2*B2
Cell B8 computes the GPA by using the following formula:
=SUM(E2:E6)/SUM(B2:B6)
The preceding formulas work fine, but you can streamline the GPA calculation quite a bit. In fact, you can use a single array formula to make this calculation and avoid using the lookup table and the formulas in columns D and E. This array formula does the job:
{=SUM((MATCH(Grades,{"F","D","C","B","A"},0)-1)*CreditHours) /SUM(CreditHours)}
Figure 15.12 shows a worksheet with a table that displays product sales by month. To retrieve sales for a particular month and product, the user enters a month in cell B1 and a product name in cell B2.
To simplify things, the worksheet uses the following named ranges:
Name | Refers To |
---|---|
Month | B1 |
Product | B2 |
Table | D1:H14 |
MonthList | D1:D14 |
ProductList | D1:H1 |
The following formula (in cell B4) uses the MATCH
function to return the position of the Month within the MonthList range. For example, if the month is January, the formula returns 2 because January is the second item in the MonthList range (the first item is a blank cell, D1).
=MATCH(Month,MonthList,0)
The formula in cell B5 works similarly but uses the ProductList range.
=MATCH(Product,ProductList,0)
The final formula, in cell B6, returns the corresponding sales amount. It uses the INDEX
function with the results from cells B4 and B5.
=INDEX(Table,B4,B5)
You can, of course, combine these formulas into a single formula, as shown here:
=INDEX(Table,MATCH(Month,MonthList,0),MATCH(Product,ProductList,0))
You can use the Lookup wizard add-in to create this type of formula. The Lookup wizard add-in is distributed with Excel. When this add-in is installed, access it by choosing Formulas Solutions Lookup.
Another way to accomplish a two-way lookup is to provide a name for each row and column of the table. A quick way to do so is to select the table and choose Formulas Defined Names Create From Selection. In the Create Names From Selection dialog box, select the Top Row and Left Column check boxes. After creating the names, you can use a simple formula, such as:
= Sprockets July
This formula, which uses the range intersection operator (a space), returns July sales for Sprockets. See Chapter 11 for details about the range intersection operator.
Some situations may require a lookup based on the values in two columns. Figure 15.13 shows an example.
The lookup table contains automobile makes and models and a corresponding code for each. The worksheet uses named ranges, as shown here:
F2:F12 | Code |
B1 | Make |
B2 | Model |
D2:D12 | Makes |
E2:E12 | Models |
The following array formula displays the corresponding code for an automobile make and model:
{=INDEX(Code,MATCH(Make&Model,Makes&Models,0))}
This formula works by concatenating the contents of Make and Model and then searching for this text in an array consisting of the concatenated corresponding text in Makes and Models.
Most of the time, you want your lookup formula to return a value. You may, however, need to determine the cell address of a particular value within a range. For example, Figure 15.14 shows a worksheet with a range of numbers that occupies a single column (named Data). Cell B1, which contains the value to look up, is named Target.
The formula in cell B2, which follows, returns the address of the cell in the Data range that contains the Target value:
=ADDRESS(ROW(Data)+MATCH(Target,Data,0)-1,COLUMN(Data))
If the Data range occupies a single row, use this formula to return the address of the Target value:
=ADDRESS(ROW(Data),COLUMN(Data)+MATCH(Target,Data,0)-1)
If the Data range contains more than one instance of the Target value, the address of the first occurrence is returned. If the Target value isn’t found in the Data range, the formula returns #N/A
.
The VLOOKUP
and HLOOKUP
functions are useful in the following situations:
You need to identify an exact match for a target value. Use FALSE as the function’s fourth argument.
You need to locate an approximate match. If the function’s fourth argument is TRUE or omitted and an exact match is not found, the next largest value less than the lookup value is returned.
But what if you need to look up a value based on the closest match? Neither VLOOKUP
nor HLOOKUP
can do the job.
Figure 15.15 shows a worksheet with student names in column A and values in column B. Range B2:B20 is named Data. Cell E2, named Target, contains a value to search for in the Data range. Cell E3, named ColOffset, contains a value that represents the column offset from the Data range.
The array formula that follows identifies the closest match to the Target value in the Data range and returns the names of the corresponding student in column A (that is, the column with an offset of –1). The formula returns Leslie (with a matching value of 8,000, which is the one closest to the Target value of 8,025).
{=INDIRECT(ADDRESS(ROW(Data)+MATCH(MIN(ABS(Target-Data)), ABS(Target-Data),0)-1,COLUMN(Data)+ColOffset))}
If two values in the Data range are equidistant from the Target value, the formula uses the first one in the list.
The value in ColOffset can be negative (for a column to the left of Data), positive (for a column to the right of Data), or 0 (for the actual closest match value in the Data range).
To understand how this formula works, you need to understand the INDIRECT
function. This function’s first argument is a text string in the form of a cell reference (or a reference to a cell that contains a text string). In this example, the text string is created by the ADDRESS
function, which accepts a row and column reference and returns a cell address.