Microsoft Office Tutorials and References
In Depth Information
Using Excel Functions
Working with Formulas and Functions
For example, suppose in cell C1 you want to find
out if the value in cell B5 is greater than the value
in cell B6. If B5 is larger than B6 (true), you want
to enter “Yes” in cell C1; if B5 is not larger than B6
(false), you want the answer “No” in cell C1. You
would enter the function as =IF(B5>B6,“Yes”,“No”).
Look at the steps needed to create an IF function
and its arguments:
In the example you see in Figure 9-16, the IF
function checks whether the total project days is less
than the goal of 125 days. If it is, Yes! is displayed;
if not, No is displayed.
1. Type =IF( in the cell in which you want to
display the answer. Excel will immediately
identify the entry as a function and display a
tip box with the function syntax.
Using the IF function.
2. Type the first argument including an
operator. This is the condition you want Excel to
Looking Up with Lookup Functions
The Lookup category contains functions designed
to save you time by finding related data. If you
work with large lists in Excel, you can use lookup
functions to retrieve individual records from those
lists quickly. Two commonly used lookup functions
are the VLOOKUP and the HLOOKUP.
3. Type a comma to begin the second argument
and then type the result you want if the
evaluation is true. The true result can be a value,
a calculation, or text.
The V stands for vertical and the H stands for
horizontal. You use VLOOKUP when you need to search
through columns of information, and you use
HLOOKUP when you need to search through rows
Enclose Text in Quotes
If you want the result to be text, the result’s
argument must be enclosed in quotation
marks. The quotation marks will not be
displayed in the answer. No quotation marks are
needed if the result is numeric.
The VLOOKUP actual syntax is VLOOKUP( lookup_
value,table_array,col_index_num ), but let’s see if I
can translate that into something a little simpler.
To understand this formula, take a look at the table
in Figure 9-17. You’ll use it to illustrate the
4. Type a comma to begin the third argument
and type the result you want if the
evaluation is false. The false result can also be a
value, a calculation, or text.
The first argument lookup_value is really asking for
where Excel finds the value you want to match, so
let’s rename it lookup cell. In this example, you
want to know how many calls were made to a
specific city, which you will enter in cell E3, so the first
argument is in cell E3.
5. Type the closing parenthesis and then press
the Enter key. Excel calculates and displays
the evaluation result.