VLookups for Management Accountants Using Two Criteria

• Author: Harlan J. Fuller
• Published date: 01 November 2014
• Date: 01 November 2014
• DOI: http://doi.org/10.1002/jcaf.22006

61

© 2014 Wiley Periodicals, Inc.

Published online in Wiley Online Library (wileyonlinelibrary.com).

DOI 10.1002/jcaf.22006

f

e

a

t

u

r

e

a

r

t

i

c

l

e

Harlan J. Fuller

VLookups for Management Accountants

Using Two Criteria

In Microsoft Excel,

VLookups are

typically used to

find information from

a table or range using

only one criterion.

But it is possible to get

information from a range or

table where you use two criteria.

In looking for ways to find a

solution from a two-dimensional

table or range using two vari-

ables or criteria, most solutions

offered use an “index” function

or a “match” function. Most

“nonpower” Excel users don’t

know what those functions are.

I think the solution is simpler

for the Excel users who already

understand the VLookup func-

tion to use a “nested” VLookup.

VLOOKUP BASICS

Some basics on the

VLookup function: VLookup is

a lookup function that requires a

table of data where each column

contains different data for mul-

tiple situations. Each of the situ-

ations is in a row. For example,

different worker’s compensation

insurance rates can be shown for

various different states. Exhibit 1

illustrates this.

I would name the range

A1:E5 “Table” for ease. I would

also name the range G1:H4

“Key.”

Typically, a VLookup func-

tion would look up the informa-

tion for one variable, such as

state. If you wanted all of the

worker’s comp insurance rates

for office workers, you could

use a VLookup, as shown in

Exhibit 2 .

VLookup asks for the

lookup value, the table array,

which column of the table array

you want the answer from, and

then “True” if the lookup value

does not have to be an exact

match to column 1 of the table,

or “False” if the lookup value

does have to be an exact match

to column 1 of the table. This

limits your variables to the par-

ticular state for which you are

looking for information.

What if you wanted to be

able to have both the state and

the job description (office, deliv-

ery, factory, or sales)

as variables?

In cells A10:A12

we have the

employee names;

column B10:B12

gives the state;

C10:C12 gives the Job Descrip-

tion; and D10:D12 gives the

Pay. The worker’s comp accrual

would be the pay in column D

times the rate from the chart in

A1:E5, which we named Table.

To make the job description

into a column number for our

VLookup, we will nest another

VLookup in the formula.

In cell E10, we are mul-

tiplying the wages in D10 by

the rate from the table. The

VLookup is using the state

in B10 to determine the row

of the table, and a nested

VLookup is using the job

description in C10 to deter-

mine the column of the table,

using the key we defined in cells

G1:H4 (see Exhibit 3 ).

After we put the formula in

E10, you can copy the formula

in E10 into E11, E12, and so on.

The nested VLookup will return

the rate from the table using the

two variables for the state and

the job description.

Excel VLookups can be useful for management

and cost accountants in many ways. This article

gives great practical examples of their use.

© 2014 Wiley Periodicals, Inc.