<<

. 18
( 62 .)



>>

instance of ˜˜Bob™™). Being able to identify a row number of a data
point can be useful in other instances, especially if we already
know which column it is in.


B C D E F G
A
1
2
Alice 4 =MATCH(“Bob”,B1:B6,0)
3
Bob
4
George
5
Bob
6
7




To make this function useful to return a row number, the
range used in LookupArray should start with row 1 in the column.
In fact, the range can be the whole column, which can be easily
defined by leaving out the row numbers, thus:


= MATCH°“Bob”,B:B,0Þ


This will identify where the first occurrence of ˜˜Bob™™ would
be in all 65,536 rows of column B.


INDEX
INDEX returns the value of a cell within a range, by locating its
row position and its column position. The syntax is:


= INDEX°Array,RowNumber,ColumnNumberÞ


The Array is any range storing the data.
The RowNumber is an integer greater than 0 that specifies
which row within the Array the data point is in. RowNumber 1
means that the data point is on the first row of the Array. This
can be omitted if the Array is a one-row range.




TLFeBOOK
Chapter 5
94




The ColumnNumber likewise is an integer greater than 0. It
specifies which column the data point is in. ColumnNumber 1
means that the data point is on the first column of the Array.
You can omit this if the Array is a one-column range.


A B C D E F
1
2
Alice 12 13
3
Bob 22 23
4
George 32 33
5
Bob 42 43
6
7



If we define the Array as B3 : D6, then INDEX(B3 : D6,1,1)
will return the value in the top left corner of the range (i.e.,
˜˜Alice™™). Using this table, we will get the following results
from these other variations:

George
= INDEX°B3:D6,3,1Þ
43
= INDEX°B3:D6,4,3Þ

When you use INDEX, the row and column specifiers must
specify positions within the Array. If they point to a location
outside the range, you will get a #REF error message.


Using MATCH and INDEX together
The power of Excel functions can be magnified by using them
together. Because MATCH can locate a row number based on
matching it with a specific label or value, you can use the
result of MATCH as a row parameter in INDEX. In the following
illustration, we specify the company ˜˜Charlie™™ in cell E7. The
following in cell E8 returns the stock price for the company:

= INDEX°D2 D5,MATCH°E7,B2
: :B5,0ÞÞ




TLFeBOOK
Your Model-Building Toolbox: Functions 95




The INDEX Array range is a one-column range, so we do not
have to specify the Column number argument.
A B C D E F G
Company Price
1
Alpha $12.25
2
Baker $8.50
3
Charlie $22.00
4
Delta $17.35
5
6
Enter company name ===> Charlie
7
Stock price: $22.00
8
Formula in E8: =INDEX(D2:D5,MATCH(E7,B2:B5,0))
9




HLOOKUP/VLOOKUP
HLOOKUP and VLOOKUP are functions that work together in
the same way as MATCH and INDEX in searching for a data
point in data range. HLOOKUP is for searching the data range
horizontally, by columns; VLOOKUP is for searching vertically,
by rows.
HLOOKUP and VLOOKUP are powerful functions and are
often used when simpler functions like OFFSET or INDEX will
do just fine. These functions are most useful when the answer
does not depend on an exact match with your search parameters.
The syntax for HLOOKUP is as follows:

= HLOOKUP°LookUpValue,TableArray,RowNumber,
LookUpTypeÞ

The LookUpValue is the value to be looked up in the first row
of the TableArray.
The TableArray contains the data for the lookup.
The RowNumber is the row that contains the data to be
returned by the function.
The LookUpType is optional. If omitted or TRUE, this means
than an approximate match can be returned if there is no
exact match. The approximate match will be based on the
value that is less than the LookUpValue.




TLFeBOOK
Chapter 5
96




A B C D E F G
Price
1
Income $0 $2,500 $25,000 $50,000
2
Tax rate: 15% 28% 31% 36%
3
4
5
6
$23,000
Enter income:
7
Applicable tax rate: =HLOOKUP(D5,B2:E3,2,TRUE)
28%
8
9


The applicable tax rate returned is 28 percent because the
income entered is over $2500 but below $25,000, and the
LookUpType entered is TRUE. If the LookUpType were set to
FALSE, the result would be an #N/A error because there is no
exact match to the entry of $23,000.
VLOOKUP uses the same syntax, but the table would have
to be arranged vertically:

A B D E F G
C
Income: Tax rate:
1
$0 15%
2
$2,500 28%
3
$25,000 31%
4
$50,000 36%
5
6
Enter income: $23,000
7
Applicable tax rate: 28% =VLOOKUP(D7,B2:C5,2,TRUE)
8




DEALING WITH ERRORS
As we develop the formulas in our model, Excel has a way of
telling us when we are going about it the wrong way. The four
most common error messages that Excel will show are the
following:
#DIV/0!
u

#VALUE!
u

#NAME?
u

#REF!
u




TLFeBOOK
Your Model-Building Toolbox: Functions 97




#DIV/0! Errors
Excel will display this error when you attempt to divide a
number by 0. It is easy to write a formula that inadvertently
divides by zero because as you develop the formula, you may
be using some test numbers. However, once you clean up the
model, these test numbers go away and you will have formulas
that then show the #DIV/0 errors. It may be that as the model
starts to be used, there will be values coming in that will make
these formulas calculate properly again. However, it is sometimes
quite disconcerting for a new user unfamiliar with your model
to see these error messages. For this reason, for any formula you
write that involves a division, you should take steps to do an
error trap by using an IF statement.
Thus, instead of the formula

= D10=D12

we should write it as:

= IF°D12,D10=D12,0Þ

Remember that D12 is the short way of writing D12<>0.

<<

. 18
( 62 .)



>>