Microsoft Excel Reference Functions

1
Microsoft Excel Reference Functions

• Objectives
• Understanding and using Reference Functions
• The computers algorithm
• Syntax

2
Reference Functions

• Functions that allow you to lookup a value
  based on a specified criteria
• Excel includes many reference functions Lookup,
  Vlookup, Hlookup, Match etc.
• Whats Johns score on the midterm?

3
LOOKUP Function

• LOOKUP(lookup_value, lookup-vector,
  result_vector)
• lookup_value - criteria to lookup or match
• lookup_vector range containing one row or
  column where you want to compare the criteria
• result_vector range containing one row or
  column (matching the lookup_vector size) that
  contains the corresponding values you wish to
  find.

4
Lookup Example column vector

• Lookup( 80,A2A6,B2B6) - Returns B
• Lookup letter grade you get with 80 points

5
Lookup Example row vector

• Lookup(80,B1F1, B2F2) returns B
• Lookup letter grade you get with 80 points

6
Could you lookup the letter grade for 67 points?

• The lookup functions algorithm will give you the
  greatest value that does not exceed the value
  being searched for.
• Lookup(67,A2A6, B2B6) results in the value D

7
Sort your vector

• Now try finding the grade for 67 points with an
  unsorted lookup_vector?
• Lookup(67, A2A6, B2B6)

• The value returned may not be correct
• The lookup algorithm requires that the values in
  the lookup_vector be placed in Ascending order

8
Consider the formula Lookup(67,A2A6,B2B6)
We dont exactly know the algorithm for a
Lookup only that we are required to sort the
lookup_vector in ascending order or the correct
answer is not guaranteed. Consider if the
algorithm were as follows

• Step 1 The computer compares 67 to 0, 80, 70
  etc. If a match is found the associated value
  will be returned. Otherwise continue to Step 2.
• Step 2 Check each value to see if its greater
  than the lookup_value
• 67gt0 is true continue to value in next row
• 67gt80 is false, the computer will go to the
  previous row, 2nd column and return F

9
Use a Reference Function when you want to find
information from a list

• The formula to find Janes age using Age1!
• The formula to name the youngest person using
  Age2!

Age2!
Age1!
10

• Use a Reference Function When you are copying
  formulas that pull information from a list -
  where that information is in a different order

Points!
Grades!
Write a formula in cell grades!C2 which can be
copied down to determine Janes letter grade.
11
Recap

• A lookup function lets you find information from
  a list based on a criteria
• need to specify a criteria
• need to specify a range where you will find that
  criteria and this range must be sorted (either
  vertically or horizontally) in ascending order
• need to specify a range where you will find the
  corresponding information