3.2 Logical and Lookup Functions
Learning Objectives
- Use an IF Function to make logical comparisons between a value and what you expect.
- Create a VLOOKUP calculation to look up information in a table.
- Understand error messages.
- Understand how to enter and format Date/Time Functions.
In addition to doing arithmetic, Excel can do other kinds of functions based on the data in your spreadsheet. In this section, we will use an =IF function to determine whether a student is passing or failing the class. Then, we will use a =VLOOKUP function to determine what grade each student has earned.
IF Function
The IF function is one of the most popular functions in Excel. It allows you to make logical comparisons between a value and what you expect. In its simplest form, the IF function says something like:
If the value in a cell is what you expect (true) – do this. If not – do that.
The IF function has three arguments:
- Logical test – Here, we can test to see if the value in a selected cell is what we expect. You could use something like “B7=14” or “B7>12” or “B7<6”
- Value_if_true – If the requirements in the logical test are met – if B7 is equal to 14 – then it is said to be true. For this argument, you can type text – “True”, or “On budget!” Or you could insert a calculation, like B7*2 (If B7 does equal 14, multiply it by 2). Or, if you want Excel to put nothing at all in the cell, type “” (two quotes).
- Value_if_false – If the requirements in the logical test are not met – if B7 does not equal 14 – then it is said to be false. You can enter the same instructions here as you did above. Let’s say that you type the double quotes here. Then, if B7 does not equal 14, nothing will be displayed in this cell.
In column Q we would like Excel to tell us whether a student is passing – or failing the class. If the student scores 70% or better, he/she will pass the class. But, if he/she scores less than 70%, he/she is failing.
-
- Make sure that Q5 is your active cell.
- On the Formulas tab, in the Function Library group, find the IF function on the Logical pulldown menu (see Figure 3.9).
Mac Users: There is no “Function Library” group for Excel for Mac. Mac Users should click on the Formulas tab, then click the “Logical” tool list arrow, and choose IF (see Figure 3.9).
Now you will see the IF Function dialog box, with a place to enter each of the three arguments.
Mac Users: There is no “dialog box”. The “Formula Builder” pane will display at the right side of the Excel window. It has the same layout as Figure 3.10 below.
- Click in the box for Logical Test. We need to test whether a student’s score is less than .7. So, in this box, type P5<.7
- Click in the box for Value_if_true. If the student’s score is less than .7, then they are failing the class. In this box, type Fail.
- Click in the box for Value_if_false. If the student’s score is NOT less than .7, then they are passing the class. In this box, type Pass.
- Make sure that your dialog box matches Figure