 |
Training Index l
Functions by category Lookup and Reference Functions
|
|
|
Excel
formula and functions that help you to lookup or refer to other other cells
based on certain criteria Below are some video clips of extremely useful
lookup functions and reference functions built into Microsoft Excel.
Be aware that these video clips use Windows Media Player. Click on the
hyperlink and, depending on your connection speed, you may need to wait a
few moments.
|
| Title |
Price
|
Video
Clip
(Windows Media Player) |
VLOOKUP
The VLOOKUP functions gives the developer the capabilities to
request that Excel reference a particular bit of information and pull
through some related information. This can either be done based on an
exact match of the criteria or an approximate match.
Excel Help File Description: Looks for a value in the
leftmost column of a table, and then returns a value in the same row
from a column you specify. By default the table must be sorted in
ascending order.
Other descriptive words: search for, find, locate, pull
through |
FREE |
|
 |
TRANSPOSE
Two methods to quickly and easily move information from row format
into a column format, or a column into a row.
Excel Help File Description: Converts a vertical range of
cells to a horizontal range, or vica versa.
Other descriptive words: change, transfer |
FREE
|
|
|
CHOOSE
The Choose function allows you to refer to different cells depending
on the results of a cell. Often useful as an alternate to the IF
function (especially if you are tempted to use nested IF functions).
Excel Help File Description: Uses index num to return a value
from the list of value arguments. Use CHOOSE to select one of up to 29
values based on the index number. For example, if value1 through
value7 are the days of the week, CHOOSE returns one of the days when a
number between 1 and 7 is used as index num. |
FREE |
CHOOSE
(3 min) |
|
OFFSET
Offset allows you to refer to cells above, below, left or right of a
reference cell. Very useful for cash flow forecasts where the receipt
or payment of money can move between periods.Excel Help File
Description: Returns a reference to a range that is a specified
number of rows and columns from a cell or range of cells. The
reference that is returned can be a single cell or a range of cells.
You can specify the number of rows and the number of columns to be
returned. |
FREE |
OFFSET
(3 min) |
|
SUMIF and COUNTIF
The Sumif and Countif commands allow you to sum or count cells
depending on whether they meet the stated criteria (NOTE that this is
actually found under the Maths and Trig
category but I believe it is more relevant here)Excel Help File
Description: Counts or sums the number of cells within a range
that meet the given criteria |
FREE |
Sumif/ Countif
(4 min) |
|
|
Quote of the page:
In theory there is no difference between theory and practice. In
practice there is.- Yogi Berra (Baseball player)
|
|
|
More FREE Excel Training? Go to the
TRAINING index page |
Do you like what you see. Tell your friends. If you have a training
coordinator
at your organisation tell them that there is free excel training available
at this site.
Don't like what you see. Tell us at
training@AuditExcel.co.za .
Do you have suggestions of what you would like to see. Tell us at
training@AuditExcel.co.za .
|
