VLOOKUP FUNCTION

1
VLOOKUP FUNCTION
Syntax VLOOKUP(valueX, TableRange, ColNum,
NotExact)
Looks DOWN the left column of TableRange for the
last row with a value lt valueX, then returns the
value in ColNum columns across within TableRange.
If NotExact is TRUE or omitted and an exact
match is not found then the largest value less
than or equal to valueX is matched, as described
above.
2
EXAMPLE 1
Example 1 VLOOKUP with numbers  
To find VLOOKUP searches down the first
column of the specified block (column A), looking
for the largest number less than or equal to 17.
It stops at cell A2, then moves across the
specified number of columns (2). It stops at cell
B2 and returns the value 56.
3
EXAMPLE 1 (CONTD)
Find the values of the following VLOOKUP(5,
A1C4, 1) VLOOKUP(50, A1C4, 3) VLOOKUP(1,
A1C4, 2) VLOOKUP(18, A1C4, 9)
VLOOKUP("18", A1C4, 3)

4
EXAMPLE 2
Example 2 VLOOKUP with text VLOOKUP can also do
a search on labels (text)
Find VLOOKUP("Tuesday",A1C4,2) Answer
5
EXAMPLE 2 (CONTD)
However, if the table is changed to
What would be the answer given by Excel
for VLOOKUP("Tuesday", A1C7,2)
VLOOKUP("Thursday", A1C7,2)
VLOOKUP("Saturday", A1C7,2)

6
EXAMPLE 2 (CONTD)
Some of the answers are not what you would
expect. WHY? For VLOOKUPs to work properly with
text fields, it is very important to include
FALSE in the NotExact field in the VLOOKUP.
Eg. VLOOKUP(Tuesday, A1C7,2, FALSE) which
means that only an exact match will do, so Excel
will keep looking down the list until an exact
match is found. What would be the right answer
now? VLOOKUP("Tuesday", A1C7,2,FALSE)
VLOOKUP("Thursday", A1C7,2, FALSE)
VLOOKUP("Saturday", A1C7,2,FALSE)

7
EXAMPLE 3
Suppose that in your business, you give a bonus
to staff if they work more than a certain number
of hours per week. The bonus table is given as
the following
How would you set up the above table in your
spreadsheet so that the bonus is given
automatically by looking up this table and the
hours worked by a staff member?
8
EXAMPLE 3
What is the vlookup formula in cell C9?
9
SPREADSHEET DESIGN

• Have an area (table) set aside to hold key
  values
• All cells in the working area then refer to the
  respective values in the Key Values Table
• By simply altering the key values different
  scenarios can be tested.
• Excel even has a scenario feature where
  different sets of key values can be stored and
  easily loaded from a scenario toolbar.
• Example of possible layouts

10
SPREADSHEET DESIGN
Another possible layout
11
CASE STUDY ORCHIDS SHADE HOUSE
A flower grower plants a bed of orchids in a
shade house at a total cost of 5000. The bed
will not start producing during the first two
years. In the third through eighth years it
should produce, respectively, 500, 1900, 2000,
2200, 2000, and 1200 dozen orchid sprays. Fixed
annual operating costs are 1000 per bed, while
variable expenses during producing years are 30
of sales receipts. If 2000 or more orchids are
produced in a year then an extra staff hand needs
to be employed, costing 15000 for that year.
Orchids currently sell for 12 per dozen, which
has currently been increasing at 4 per year.
Construct a spreadsheet model that shows a
breakdown of anticipated annual income and
expenses over the eight years.
12
CASE STUDY ORCHIDS SHADE HOUSE