INF264
Study Unit 5
Retrieving Data for Computation,
Analysis, and Reference
Succeeding in Business with Microsoft Excel 2013 ©
Study Unit 5 Introduction
In previous units, you learned how to create formulas
with IF and nester IF functions to make decisions based
on specified criteria. However these formulas are not
well suited in some circumstances, such as selecting a
particular value from a long list of values. For such cases,
there are Excel functions known as Reference and
Lookup functions. You will use these functions to
retrieve data stored in the same or in a different
worksheet, and then use that data in formulas.
P1
Level 1 begins with the basic VLOOKUP and HLOOKUP
functions.
In Level 2, the LOOKUP function is used to retrieve a
value in a column or row, and the INDEX function is used
to to look up a value in a two-dimensional range.
Level 3 shows how to nest various reference and
lookup functions, including CHOOSE and MATCH, to
perform more complex calculations.
P2
To go to Level 1 , click here: Level 1 Objectives :
To go to Level 2 , click here: Level 2 Objectives
Level 1 Objectives: :
To go to Level 3 , click here: Level 3 Objectives :
P3
Functions Covered in This Study Unit
• CHOOSE
• HLOOKUP
• IFERROR
• INDEX
• ISBLANK
• LOOKUP
• MATCH
• VLOOKUP
P4
Level 1 Objectives:
Performing Basic Lookups to Calculate
and Evaluate Data
• Organize and evaluate data in vertical and
horizontal lookup tables
• Understand the VLOOKUP and HLOOKUP
function rules
• Retrieve data from a vertical lookup table
• Retrieve data from a horizontal lookup table
P5
Working with Lookup Tables
• Lookup tables
– Contain data that can be used to create worksheets
that list items, and then perform calculations
– Automate the process of looking up data to use in
calculating unit and total prices
P6
Working with Lookup Tables (continued)
P7
Table
• VLOOKUP function
– The most effective and flexible way to retrieve data
organized in columns
– Searches a specified part of a worksheet for data,
starting with the first column
– =VLOOKUP(lookup_value,table_array,
col_index_num,range_lookup)
– Must specify col_index_num
P8
P9
Results of using the VLOOKUP function
P 10
• Steps that Excel performs when the VLOOKUP
type is TRUE and key data is sorted in
ascending order:
1. Look for an exact match
2. Check the first value in the lookup table
3. Check the next value in the lookup table
4. Check the last value in the lookup table
P 11
• If using a type FALSE, the VLOOKUP function
looks only for an exact match of the lookup
value
• Values in a lookup table need not be sorted in
ascending order
• The VLOOKUP function displays #N/A in a cell if
an exact match is not found
P 12
P 13
Table
• HLOOKUP function
– Looks up a value by testing for a criterion across a
row
– =HLOOKUP(lookup_value,table_array,
row_index_num,range_lookup)
– Must specify row_index_num
P 14
P 15
P 16
• HLOOKUP type TRUE algorithm
– Works in the same way as for the VLOOKUP
function, except that values are tested across the
row (instead of down the first column)
– The first row of the lookup table must be sorted in
ascending order
P 17
Using the HLOOKUP function
P 18
Level 1 Summary
• VLOOKUP function
– Use when the first column of the lookup table contains
the key data, and information you want to retrieve is in
a column to the right of the key data column
• HLOOKUP function
– Similar to VLOOKUP, except that it retrieves data stored
in horizontal lookup tables
– Use when the first row of the lookup table contains the
key data, and the information you want to retrieve is in
a row before the key data row
P 19
Level 2 Objectives:
Performing More Complex Lookups Involving
Multiple Worksheets and Multidimensional Tables
• Analyze and retrieve data from multiple
worksheets
• Look up data in a one-row or one-column range
• Use named range references in formulas
• Retrieve data from multidimensional tables
P 20
P 21
Retrieving Data from Multiple Worksheets
• Use VLOOKUP and HLOOKUP formulas
– This is especially useful when lookup tables are
long
P 22
Using VLOOKUP with multiple worksheets
P 23
P 24
One-Column Range
• Use the LOOKUP function
– Looks up the greatest value that does not exceed a
specified value anywhere in a table or range
– Can retrieve data from a lookup table with a
vertical or horizontal orientation
– Uses only a TRUE lookup type; the column or row
containing lookup values must be in ascending
order
– =LOOKUP(lookup_value,lookup_vector,
result_vector)
P 25
P 26
P 27
Multidimensional Tables
• Use the INDEX function with two- or
threedimensional tables
– Returns the value in a table based on the row and
column numbers that you specify
– =INDEX(reference,row_num,column_num,
area_num)
– Has several guidelines
P 28
P 29
Using an INDEX function with a two-dimensional table
P 30
Using an INDEX function with a three-dimensional table
P 31
Level 2 Summary
• LOOKUP function
– Looks up the greatest value that does not exceed a
specified value anywhere in the table or range
– Can retrieve data from a lookup table with a
vertical or horizontal orientation
• INDEX function
– Allows retrieval of data from multidimensional
tables
P 32
Level 3 Objectives:
Nesting Lookup and Reference Functions to
Retrieve and Calculate Data
• Prevent errors in data retrieval
• Nest lookup and reference functions to perform more
complex calculations
• Choose a value or a range of values for analysis
• Retrieve data by matching the relative position of an
item in a list
P 33
Refining the Order Form
P 34
Preventing Errors in Data Retrieval
• IS functions
– Nine functions that test a value or cell reference,
and then return a TRUE or FALSE value depending
on the results
– Often used in formulas to test the outcome of a
calculation
– When combined with the IF function, they help
locate data-entry errors
– =ISBLANK(value)
P 35
P 36
Calculate the Price per Unit
P 37
P 38
• CHOOSE function
– A Reference and Lookup function that can return a
value or a range for up to 254 different values
– =CHOOSE(index_num,value1,value2,…)
P 39
Using the CHOOSE function to determine the table_array
P 40
Determining the col_index_num
P 41
Creating the nested VLOOKUP formula
P 42
Calculating Totals
P 43
Calculating the Discount Amount
P 44
MATCH and INDEX Functions
• INDEX function
– =INDEX(reference,row_num,column_num,
area_num)
• MATCH function
– Designed to return the relative position of an item
in a list
– =MATCH(lookup_value,lookup_array, match_type)
– Has several guidelines
P 45
P 46
• Using the INDEX function:
– Determine the reference argument of the INDEX
function
– Determine the row_num of the INDEX function
– Determine the column_num of the INDEX function
by using the MATCH function
– Determine the area_num of the INDEX function
P 47
P 48
P 49
Level 3 Summary
• To solve more complex problems, nest
Reference and Lookup functions using the IF,
IS, INDEX, CHOOSE, and MATCH functions
• ISBLANK
– One of nine IS functions, which all check for a
condition and then return a TRUE or FALSE value
– Checks whether a specified value refers to an
empty cell
P 50
Level 3 Summary (continued)
• INDEX
– Returns the value in a table based on specified row
and column numbers
• CHOOSE
– Returns a value/range for up to 29 corresponding
values
• MATCH
– Returns the relative position of an item in a list
P 51
Study Unit Summary
• Performing basic lookups to calculate and
evaluate data
• Performing more complex lookups involving
multiple worksheets and multidimensional
tables
• Nesting lookup and reference functions to
retrieve and calculate data
P 52