Lookup Tables 177
column of the lookup table. The second argument specifies a range of cells where the
table is stored in the worksheet. The third argument specifies the column number from
which the value returned by the function is taken. The fourth argument is an optional
argument. It can be either TRUE or FALSE. If it is omitted, its value is treated as TRUE.
When this argument is TRUE, the VLOOKUP function will return the result based on an
approximate match if an exact match is not present. The approximate match is taken as
the highest value that is less than the lookup value. Excel requires that the values in the
first column of the lookup table be in ascending order if the range_lookup argument
is given as TRUE or is omitted. When the optional argument is set to FALSE, the func-
tion searches for an exact match only and returns #N/A if no match is found.
As an example of VLOOKUP function, consider the information shown in Table 7.1.
The table has information organized in three columns. For each person, we have the
name in the first column, phone number in the second column, and address in the
third column. The formula =VLOOKUP("Jack",A2:C6,2) will return 734-111-
, the phone number of Jack, because “Jack” is the value the function looks for
in the first column of the table and it looks for a corresponding value in column
two. If we change the formula to =VLOOKUP("Jack",A2:C6,3), then the value
returned by the formula will be 9012 Noway, Jack’s address from the third column.
Suppose we made an error while entering the lookup value in our formula and entered
“Jeck” in the formula =VLOOKUP("Jeck",A2:C6,3). We will still get the result
9012 Noway because of the approximate match. However, if we enter the formula
=VLOOKUP("Jeck",A2:C6,3,FALSE), we are forcing an exact match and the value
returned by the formula will be #N/A.
The HLOOKUP function works in a similar manner except that the lookup table is orga-
nized along rows. As an example of the HLOOKUP function, let us consider the lookup
table shown in Table 7.2. The table consists of two rows. The first row, marked Score,
carries cutoff scores and the second row carries the corresponding letter grades. The
value returned by the formula =HLOOKUP(256,B1:F2,2) will be C. This is because
the lookup value of 256 is not present in the table and an approximate match is allowed.
The highest value that is less than 256 is 200 in the table. Thus, the function will return
the value from the corresponding position in the second row.
Table 7.1 Address Book Data
Name Phone # Address
Bill 248-555-1212 1234 Freeway
Chad 313-999-3131 5678 Highway
Jack 734-111-2222 9012 Noway
Pam 810-100-1234 3456 Everyway
Rani 248-888-0123 7890 Beltway