Class Notes
(786,073)

Canada
(481,982)

University of Waterloo
(17,964)

Computer Science
(739)

CS 100
(114)

Dan Brown
(12)

Lecture

# Module 3 notes.docx

Unlock Document

University of Waterloo

Computer Science

CS 100

Dan Brown

Winter

Description

Module 3 notes
3.1 The VLOOKUP Function
VLOOKUP (vertical lookup) provides this functionality for a table in which each row represents one entry.
In general, VLOOKUP takes four parameters as follows:
1. the value to match in the lookup table (the search key) usually the search key is stored in some cell, and the
address of the cell containing the search key is given as the first parameter to VLOOKUP
2. the table to search for a match (the key is matched against the leftmost column) The table specified in the
second parameter is usually a named range that you must define prior to entering the VLOOKUP function in a
formula.
3. the column in which to find the value to be returned (the leftmost column is column 1) The first column of the
table contains possible keys to match, and the other columns contain possible return values. The third
parameter must be a column number between 1 and the number of columns in the table. It is the contents of
the cell in that column of the matching row that is returned as the value of VLOOKUP.
4. whether the closest match is to be found (TRUE) or an exact match is required (FALSE)The fourth parameter
simply states, “Should I find the closest match, or is an exact match required?” FALSE, as used in this example,
means an exact match to the key must be found in the first column of the table.
5. Excel CALC[1]
“?” “.” matches any
single character
“*” “.*” matches any
sequence of
zero or more
characters
“~?” “\?” matches a
question mark
“.” “\.” matches a period
“~*” “\*” matches an
asterisk
For example, “sm?th*” in Excel (or “sm.th.*” in CALC) matches “Smith” or “Smythe” or “smatherson” and so forth;
“sm*th*” (or “sm.*th.*” in CALC) matches any of those as well as “smoothly” and so forth. The pattern “*~? *” in
Excel (or “.*\? .*” in CALC) matches any label that has a question mark followed by a blank (space character)
somewhere within it. (Pattern matching cannot be used to match cell contents stored as numeric values.)
3.1.1 Exact match searching
The fourth parameter for VLOOKUP indicates what to do when a match for the search key specified is not found in the
table. If the fourth parameter is FALSE and the search key is not found, then VLOOKUP returns #N/A; this result can be
handled by using the ISNA function
When exact matching is specified, it is possible that your search key will match more than one value in the first
column of the table, especially if you are searching with a label pattern; in this case, the first entry to match (i.e.,
the match nearest the top of the table) is returned by VLOOKUP.
3.1.2 Closest match searching
When the fourth parameter is TRUE (or it is omitted altogether), “Price is Right”[2] matching is used: the values in
the leftmost column of the table (the “bids”) are compared against the search key (the “price”) to find the row
containing the greatest value not exceeding the search key.
* To use lookup with inexact matching, the table must be sorted in increasing order of the value in its
leftmost column.
3.2 Indirect addressing
To make the application treat a label as a cell address or range of cell addresses, or as the name of a
cell or a cell range, you must use the function INDIRECT. In its simplest form (and the only one we will
use in CS 100), INDIRECT takes a label as its only parameter and treats that label as if it were a cell
address or cell range. The result can then be used in another function if you wish. For example, using the function INDIRECT("A5") causes the label "A5" to be treated as if it were the address of a cell,
namely A5. The formula =INDIRECT(C2) treats the contents of C2 as if it were the address of a cell
(i.e., the value you want is not in cell C2, instead the contents of C2 tells you where to fetch the
value you want); the formula returns the contents of the cell that has the address specified by the
label stored in cell C2 (e.g., if C2 contains the label "G14", then the formula returns the contents of
cell G14; if C2 contains the label "SalesTax", then the formula returns the contents of the cell named
SalesTax).
INDIRECT Takes one parameter (a
label) and interprets that
label as a cell address
(e.g., A4), a cell range
(e.g., A4:B17), or a name
assigned to a cell or to a
cell range.
SUMPRODUCT Takes two parameters (two
cell ranges of equal shape)
and computes the sum of
the products of the
corresponding cell values
in each range.
3.3 Spreadsheet Design
A spreadsheet is a convenient way to perform complex calculations, especially when those calculations are
highly repetitive. However, to be useful a spreadsheet must beverifiable and maintainable. This means that
a spreadsheet is not only a communications medium between its creator and the computer, but it is also a
communication medium among its creator, maintainer(s), and all its users. A spreadsheet will often be
useful over several years, and so e

More
Less
Related notes for CS 100