IF statements

IF statements
IF statements allow you to make logical comparisons between conditions. An IF statement generally says that if one condition is true do something, otherwise do something else. The formulas can return text, values, or even more calculations.
In cell D9
Copy D9 to IF statements allow you to make logical comparisons between conditions. An IF statement
Try anothergenerally says that if one condition is true do something, otherwise do something else. The
IMPORTANT formulas can return
DETAILTRUE text, are
and FALSE values, orother
unlike even words
more in
calculations.
Excel formulas in that they don't need to be in quotes, and Excel will automatically capitalize them. Numbers don't need to be in quotes either. Regular text, like Yes or No does need to be in quotes like this: =IF(C3="Apple","Yes","No")
Dive down for more detail
Next IF statements
IF statement1with another
In cell D9 enter =IF(C9="Apple",TRUE,FALSE). The correct answer is TRUE.
function Apple
IF statements can also force additional calculations to be performed if a certain condition is met. Here we're going to e Orange
In cell F33,
Next we've 2 Copy D9 to D10. The answer here should be FALSE, because an orange is not an 50 Less than 100
Next, change apple.
GOOD TO KNOWWhen you create a formula, Excel will automatically place colored borders around any ranges referenced in the formula, and the corresponding ranges in the formula will be the same color. You can see this if you select cell F33 and press F2 to edit the formula.
EXPERT TIP 3 Try another example by looking at the formula in cell D12. We got you started IMPORTANT DETAIL
Previous with =IF(C12<100,"Less than 100","Greater than or equal to 100"). What TRUE and FALSE are unlike other words in Excel formulas in
Next happens if you enter a number greater than or equal to that they don't need to be in quotes, and Excel will
More information on 100 inweb
the cell C12? automatically capitalize them. Numbers don't need to be in
All about the IF function quotes either. Regular text, like Yes or No does need to be
All about the IFS function in quotes like this:
Advanced IF statementsDive down for more detail Next =IF(C3="Apple","Yes","No")
Free Excel training online

IF statement with another function

Item Quantity Cost Total
IF statements can also force additional calculations to be performed if a certain condition Widget 2 $9,76 $19,52
is met. Here we're going to evaluate a cell to see if Sales Tax should be charged, and Doohickey 3 $3,42 $10,26
calculate it if the condition is true.
Sub-Total $13,18 $29,78
1 In cell F33, we've entered =IF(E33="Yes",F31*SalesTax,0), where we set up
SalesTax as a Named Range with a value of 0.0825. Our formula says If cell E33 Sales Tax? Yes $2,46 GOOD TO KNOW
equals Yes, then multiply cell F31 times SalesTax, otherwise return a 0. When you create a formula, Excel will automatically
Shipping? Yes $6,25 place colored borders around any ranges referenced in
the formula, and the corresponding ranges in the
Try changing Yes to No in cell E33 to see the calculation change.
formula will be the same color. You can see this if you
Total $38,48 select cell F33 and press F2 to edit the formula.
2 Next we've added an IF statement to calculate shipping if it's required. In cell
F35 you'll see =IF(E35="Yes",SUM(D28:D29)*1.25,0). This says "If cell E35 is
Yes, then take the sum of the Quantity column in the table above, and multiply
it by 1.25, otherwise return a 0".

3 Next, change the 1.25 in the formula in cell F35 to "Shipping". As you start EXPERT TIP
typing, Excel's auto-correct, should find it for you. When it does, press Tab to Named Ranges allow you to define terms or values in
enter it. This is a Named Range, and we entered it from Formulas > Define a single place, and then reuse them throughout a
Name. Now, if you ever need to change your shipping cost, you only have to do workbook. You can see all of the named ranges in this
it in one place, and you can use the Shipping name anywhere in the workbook. workbook by going to Formulas > Name Manager.
Click here to learn more.

More information on the web

All about the IF function

All about the IFS function

Advanced IF statements

Free Excel training online

VLOOKUP
VLOOKUP
VLOOKUP is one of the most widely used functions in Excel (and one of our favorites too!). VLOOKUP lets you look up a value in a column on the left, then returns information in another column to the right if it finds a match. VLOOKUP says:
=VLOOKUP(A1,B:C,2,FALSE)
What do youVLOOKUP
want to is one
look of the most widely used functions in Excel (and one of our favorites too!).
for?
If you findVLOOKUP
it, how manyletscolumns
you look up aright
to the valuedoin
youa column on the
want to get left, then returns information in
a value?
Where doanother
you wantcolumn
to look toforthe
it? right if it finds a match. VLOOKUP says:
Do you want an exact, or approximate match?
What do you If you find it, how many
In cell D22,
want to look columns to the right do
Now try for
for? you want to get a value?
EXPERIMENTTry selecting different items from the drop down lists. You'll see the result cells instantly update themselves with new values.
Dive down for more detail
Next
VLOOKUP and #N/A =VLOOKUP(A1,B:C,2,FALSE)
Invariably, you'll run into a situation where VLOOKUP can't find what you asked it to, and it returns an error (#N/A). Sometimes, it's because the lookup value simply doesn't exist, or it can because the reference cell doesn't have a value yet.
If you know
If you're not Where do you Do you want Fruit Amount Meat Amount
IMPORTANT DETAILIFERROR is what's known as awant blanket error handler,
to look an meaning
exact, or it will suppress any error your formula Apples 50 Beef 50
Previous for it? approximate Oranges 20 Chicken 30
Next match? Bananas 60 Pork 10
More information on the web Lemons 40 Fish 50
1
All about the VLOOKUP In
All about the INDEX/MATCH
function
cell D22, enter
functions
=VLOOKUP(C22,C17:D20,2,FALSE). The correct answer for
Apples Pork
Apples is 50. VLOOKUP looked for Apples, found it, then went over one column
All about the IFERROR to function
the right, and returned the amount.
Use PivotTables to analyze worksheet data
2 Now try for yourself in the Meat section, in cell G22. You should end up with
Free Excel training online EXPERIMENT
=VLOOKUP(F22,F17:G20,2,FALSE). Try selecting different items from the drop down lists. You'll see
the result cells instantly update themselves with new values.

Dive down for more detail Next

VLOOKUP and #N/A If the SUM function in cell D42 could talk, it would say this: Sum up the values in cells D38, D39, D40, and D41.
Item Amount Item Amount
Invariably, you'll run into a situation where VLOOKUP can't find what you asked it to, and it Bread 50 Bread 50
returns an error (#N/A). Sometimes, it's because the lookup value simply doesn't exist, or Donuts 100 Donuts 100
it can because the reference cell doesn't have a value yet. Cookies 40 Cookies 40
Cakes 50 Cakes 50
1 If you know your lookup value exists, but want to hide the error if the lookup cell Pies 20 Pies 20
is blank, you can use an IF statement. In this case, we'll wrap our existing
VLOOKUP formula like this in cell D43: #N/A Pastry

=IF(C43="","",VLOOKUP(C43,C37:D41,2,FALSE))

This says, "If cell C43 equals nothing (""), then return nothing, otherwise return the IMPORTANT DETAIL
VLOOKUP's results". Note the second closing parenthesis at the end of the IFERROR is what's known as a blanket error handler, meaning it
formula. This closes the IF statement. will suppress any error your formula might throw. This can
cause problems if Excel is giving you a notification that your
2 If you're not sure your lookup value exists, but you still want to suppress the formula has a legitimate error that needs to be fixed.
#N/A error, you can use an error handling function called IFERROR in cell G43:
=IFERROR(VLOOKUP(F43,F37:G41,2,FALSE),""). IFERROR says, "If the A rule of thumb is to not add error handlers to your formulas
VLOOKUP returns a valid result, then display that, otherwise, display nothing until you're absolutely certain they work properly.
("")". We displayed nothing here (""), but you can also use numbers (0,1, 2, etc.),
or text, such as "Formula isn't correct".

More information on the web

All about the VLOOKUP function

All about the INDEX/MATCH functions

All about the IFERROR function

Use PivotTables to analyze worksheet data

Free Excel training online