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.