Microsoft Excel 2016 BIBLE (2016)

IN THIS CHAPTER

1. Introducing formulas that look up values in a table

2. Identifying the worksheet functions used to perform lookups

3. Getting acquainted with basic lookup formulas

4. Delving into more sophisticated lookup formulas

This chapter discusses various techniques that you can use to look up a value in a range of data. Excel has three worksheet functions (LOOKUP, VLOOKUP, and HLOOKUP) designed for this task, but you may find that these functions don't quite cut it.

I provide many lookup examples, including alternative techniques that go well beyond the Excel program's normal lookup capabilities.

Introducing Lookup Formulas

A lookup formula returns a value from a table by looking up another related value. A common telephone directory (remember those?) 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.

Note

I use the term table to describe any rectangular range of data. The range does not necessarily need to be an “­official” table, as created by choosing InsertTablesTable.

Figure 14.1 shows a worksheet that uses four 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. If the last name does not appear in column C, the formulas return #N/A.

Figure 14.1 Lookup formulas in row 2 look up the information for the employee name in cell C2.

The following lookup formulas use the VLOOKUP function:

1. D2 =VLOOKUP(C2,EmpData,2,FALSE)

2. E2 =VLOOKUP(C2,EmpData,3,FALSE)

3. F2 =VLOOKUP(C2,EmpData,4,FALSE)

4. G2 =VLOOKUP(C2,EmpData,5,FALSE)

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.

Note

Most of the examples in this chapter use named ranges for function arguments. When you adapt these formulas for your own use, you need to substitute the actual range address or a range name defined in your workbook.

Functions Relevant to Lookups

Several Excel functions are useful when writing formulas to look up information in a table. Table 14.1 lists and describes these functions.

Table 14.1 Functions Used in Lookup Formulas

^* Introduced in Excel 2007.

The examples in this chapter use the functions listed in Table 14.1.

Using the IF Function for Simple Lookups

The IF function is versatile and is often suitable for simple decision-making problems. The accompanying figure shows a worksheet with student grades in column B. Formulas in column C use the IF function to return text: either Pass (a score of 65 or higher) or Fail (a score below 65). For example, the formula in cell C2 is

=IF(B2>=65,"Pass","Fail")

You can “nest” IF functions to provide even more decision-making ability. This formula, for example, returns one of four strings: Excellent, Very Good, Fair, or Poor.

=IF(B2>=90,"Excellent",IF(B2>=70,"Very Good",IF(B2>=50,"Fair","Poor")))

This technique is fine for situations that involve only a few choices. However, using nested IF functions can quickly become complicated and unwieldy. The lookup techniques described in this chapter usually provide a much better solution.

Basic Lookup Formulas

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

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.

Caution

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.

Tip

If the lookup_value argument is text and the range_lookup argument is FALSE, the lookup_value can include wildcard characters * and ?.

A common use for a lookup formula involves an income tax rate schedule (see Figure 14.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)

Figure 14.2 Using VLOOKUP to look up a tax rate.

The examples in this section are available at this book's website at www.wiley.com/go/excel2016bible. 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

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, HLOOKUP will search for an exact match. If HLOOKUP can't find an exact match, the function returns #N/A.

Tip

If the lookup_value argument is text and the range_lookup argument is FALSE, the lookup_value can include wildcard characters * and?.

Figure 14.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)

Figure 14.3 Using HLOOKUP to look up a tax rate.

The LOOKUP function

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).

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.

Caution

Values in the lookup_vector must be in ascending order. If the values in the first column are not arranged in ascending order, the LOOKUP function may return an incorrect value. If lookup_value is smaller than the smallest value in lookup_vector, LOOKUPreturns #N/A.

Figure 14.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)

Figure 14.4 Using LOOKUP to look up a tax rate.

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.

Combining the MATCH and INDEX functions

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 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

Note

If array contains only one row or column, the corresponding row_num or column_num argument is optional.

Figure 14.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))

Figure 14.5 Using the INDEX and MATCH functions to perform a lookup.

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.

When a Blank Is Not a Zero

The Excel lookup functions treat empty cells in the result range as zeros. The worksheet in the accompanying figure contains a two-column lookup table, and this formula looks up the name in cell B1 and returns the corresponding amount:

=VLOOKUP(B1,D2:E8,2)

Note that the Amount cell for Charlie is blank, but the formula returns a 0.

If you need to distinguish zeros from blank cells, you must modify the lookup formula by adding an IF function to check whether the length of the returned value is 0. When the looked-up value is blank, the length of the return value is 0. In all other cases, the length of the returned value is nonzero. The following formula displays an empty string (a blank) whenever the length of the looked-up value is zero and the actual value whenever the length is anything but zero:

=IF(LEN(VLOOKUP(B1,D2:E8,2))=0,"",(VLOOKUP(B1,D2:E8,2)))

Alternatively, you can specifically check for an empty string, as in the following formula:

=IF(VLOOKUP(B1,D2:E8,2)="","",(VLOOKUP(B1,D2:E8,2)))

Specialized Lookup Formulas

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 this book's website at www.wiley.com/go/excel2016bible. The file is named specialized lookup examples.xlsx.

Looking up an exact value

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 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 14.6 shows a worksheet with a lookup table that contains employee numbers (column D) and employee names (column E). 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)

Figure 14.6 This lookup table requires an exact match.

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.

Tip

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 works only with Excel 2007 and later versions. For compatibility with previous versions, use the following formula:

=IF(ISNA(VLOOKUP(B1,EmpList,2,FALSE)),"Not Found",VLOOKUP(B1,EmpList,2,FALSE))

Looking up a value to the left

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 14.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 VLOOKUPfunction won't work because the data isn't arranged correctly. One option is to rearrange your data, but sometimes that's not possible.

Figure 14.7 The VLOOKUP function can't look up a value in column B based on a value in column C.

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 LOOKUP 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 serious 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 incorrect result — and you won't even know it.

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 don't have to be sorted.

=INDEX(Averages,MATCH(LookupValue,Players,0))

Performing a case-sensitive lookup

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 14.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).

Figure 14.8 Using an array formula to perform a case-sensitive lookup.

The array formula that follows is in cell B2. This formula performs 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)

Note

When entering an array formula, remember to press Ctrl+Shift+Enter, and do not type the curly brackets.

Looking up a value from multiple lookup tables

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 14.9 shows an example.

Figure 14.9 This worksheet demonstrates the use of multiple lookup tables.

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

Determining letter grades for test scores

A common use of a lookup table is to assign letter grades for test scores. Figure 14.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.

Figure 14.10 Looking up letter grades for test scores.

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 14.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. Instead, the information in the lookup table is hard-coded into an array:

=VLOOKUP(B2,{0,"F";40,"D";70,"C";80,"B";90,"A"},2)

See Chapter 17, “Introducing Array Formulas,” for more information about arrays.

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"})

Note that in this case, you do type the curly brackets because the arrays are arguments for the function. The formula itself is not an array formula.

Calculating a grade-point average

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 14.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 Credit Hours. 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)

Figure 14.11 Using multiple formulas to calculate a GPA.

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)}

Performing a two-way lookup

Figure 14.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.

Figure 14.12 This table demonstrates a two-way lookup.

To simplify things, the worksheet uses the following named ranges:

· 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 combine these formulas into a single formula, as shown here:

=INDEX(Table,MATCH(Month,MonthList,0),MATCH(Product,ProductList,0))

Tip

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 this:

= Sprockets July

This formula, which uses the range intersection operator (a space), returns July sales for Sprockets.

See Chapter 10, “Introducing Formulas and Functions,” for more about the range intersection operator.

Performing a two-column lookup

Some situations may require a lookup based on the values in two columns. Figure 14.13 shows an example.

Figure 14.13 This workbook performs a lookup by using information in two columns (D and E).

The lookup table contains automobile makes and models and a corresponding code for each. The worksheet uses named ranges, as shown here:

· Code: F2:F12

· Make: B1

· Model: B2

· Makes: D2:D12

· Models: E2:E12

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.

Determining the cell address of a value within a range

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 14.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.

Figure 14.14 The formula in cell B2 returns the address in the Data range for the value in cell B1.

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.

Looking up a value by using the closest match

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 14.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.

Figure 14.15 This workbook demonstrates how to perform a lookup by using the closest match.

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 Paul (with a corresponding value of 6,800, which is the one closest to the Target value of 7,200).

{=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 theADDRESS function, which accepts a row and column reference and returns a cell address.