3.2 Logical and Lookup Functions
Learning Objectives
- Use an IF Function to make logical comparisons between a value and what you expect.
- Become familiar with the AND and OR logical functions.
- Become familiar with and AND function nested inside an IF function.
- 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. Look at how to use AND and OR logical functions and nested functions. 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 3.10.
While we are here, let’s take a look at the dialog box. Notice that as you click in each box, Excel gives you a brief explanation of the contents (in the middle below the boxes.) In the lower left-hand corner, you can see the results of the calculation. In this case, DeShae is passing the class. Below that is a link to Help on this function. Selecting this link will take you to the Excel help for this function – with detailed information on how it works.
- Once you have typed in the required arguments and reviewed to make sure they are correct, press OK.
Mac Users should click the “Done” button, then close the Formula Builder pane.
(The text Pass should be displayed in Q5 because DeShae is passing the class.) - Use the Fill handle to copy the IF function down through row 24.
- Click on Q5. When you look in the formula bar, you will see the IF calculation: =IF(P5<0.7,”Fail”,”Pass”) (see Figure 3.11).
AND AND OR FUNCTIONS
The AND and OR functions are two more logical functions in Excel. The AND function checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE. If not all arguments are true, it returns FALSE.
The OR function checks whether any of the arguments are TRUE, and returns TRUE if any of the arguments are TRUE. It only returns FALSE if all of the arguments are FALSE.
Both the AND and OR functions have similar structures:
AND(Logical1, Logical2, Logical 3, …)
OR(Logical1, Logical2, Logical3, …)
where Logical1, Logical2, etc. are the conditions that are either TRUE or FALSE.
(Note: You will not be completing this in your practice file.) In our gradebook scenario, we could test to see if a student earned an A grade on the Final Exam and they also earned enough points to receive an A as their final grade. To receive an A grade on the Final Exam a student must score 135 points or higher. To receive an A as their final grade the student must earn 351 points or more.
The function would look like this: =AND(N2>=135,O2>=351), where N2 is the Final Exam score and O2 is the Total Points earned. See Figure 3.12.
NESTING FUNCTIONS
In addition to using functions on their own, you can nest a function inside of another function. For example, you could nest an AND function inside of an IF function’s logical test.
In the grade scenario, it’s the beginning of the term, and we want to send an email to any student who scored less than a C (less than 7 points) on both of the first two chapters. If a student receives less than 7 points on both Chapter 1 and Chapter 2, we want the IF function to return the value “Yes,” if not, then return the value “No.”
Our nested function would look like this: =IF(AND(A5<7,B5<7),”Yes”,”No”), where A5 is the points for Chapter 1 and B5 is the points for Chapter 2. (Note: You will not completing this in your practice file.) See Figure 3.13.
If you’re using the type in method of entering a function, simply type in the AND function name where you would enter the logical test. If you are using the Functions Arguments dialog box to build your function, with your insertion point in the logical test box, click the arrow in Excel’s name box and select the AND function, or whichever function you want to nest. If the function you want is not shown in the list, select the “More Functions” option.
VLOOKUP Function
You need to use a VLOOKUP function to look up information in a table. Sometimes that table is on a different sheet in your workbook. Sometimes it is in another file entirely. In this case, we need to know what grade each student is getting based on their percentage score. You will find the table that defines the scores and the grades in A28:B32.
There are four pieces of information that you will need in order to build the VLOOKUP syntax. These are the four arguments of a VLOOKUP function:
- The value you want to lookup, also called the Lookup_value. In our example, the lookup value will be the student’s percentage score in column P.
- The Table_array is the range (table) where the lookup values and the values you want returned by the function are located. In our example, this is the table of percentages and corresponding letter grades in the range A28:B32. The lookup value should always be in the first column in the table array for VLOOKUP to work correctly. For example, in our table_array the lookup value is in cell A28, so the range should start with A.
- The Col_index_num is the column number in the range that contains the value to return. In our example, when you specify A28:B32 as the Table_array range, you should count A as the first column (1), B as the second column (2), and so on. You will enter the appropriate column number in this box as 1, 2, or 3 and so on.
- In the Range_lookup, you can optionally specify TRUE if you want an approximate match or FALSE if you want an exact match of the return value. If you leave this blank, the default value will always be TRUE, or approximate match.
Let’s create the VLOOKUP to display the correct Letter Grade in column R.
- Make sure that R5 is your active cell.
- On the Formulas tab, in the Function Library, find the VLOOKUP function on the Lookup & Reference pull-down menu (see Figure 3.14).
Mac Users should click the Lookup and Reference tool list arrow to find the VLOOKUP function. - Fill in the dialog box so that it looks like the image in Figure 3.15.
Mac Users will use the “Formula Builder” pane at the right side of the Excel Window.
-
- Lookup_value – In this case, we will use the Percentage score. So, P5 for the first lookup value.
- Table_array – This is the range that contains the value you want returned by the function. In this case, that range is A28:B32. Note that this range does NOT include the label in row 27; just the actual data. The cell references for the Table_array need to be absolute – $A$28:$B$32. When we copy this function to the other cells, we do not want these cell references to change. It should always be $A$28:$B$32. This is very important! They must have the absolute reference symbols or the calculations will not work.
- Col_index_number – This is the column in the table array range that includes the information that we are looking up. In our case, the actual grades are in the 2nd column of the range. So, the column index will be 2.
- Range_lookup – In some cases, you will need something in the Range_lookup box. Since we are looking for an approximate match for the percentages, we want the default value of TRUE, so we do not need to enter anything for this argument.
- While you are in the dialog box, be sure to look at all the helpful definitions that Excel offers.
- When you have filled in the dialog box, press OK.
Mac Users should click the “Done” button, then close the Formula Builder pane. - The calculation you will see in the formula bar is: =VLOOKUP(P5,$A$28:$B$32,2)
- Use the fill handle to copy the function down through row 24. The results displayed should match Figure 3.16.
Note: What if it didn’t work? What if you get a result different from the one predicted? In this case, either you have made a previous error, resulting in different % scores than this exercise anticipated, or you made a mistake entering your VLOOKUP function.
To make repairs in the function, make sure that R5 is your active cell. On the Formula bar, press the Insert Function button (see Figure 3.17). That will reopen the dialog box so you can make your repairs. Did you forget to make the cell references for the Table_array absolute? Did you use the wrong cell for the Lookup_value? Press OK when you are done and recopy the corrected function.
Error Messages
In Chapter 2 we discussed the various error messages that occur when working with formulas and functions. If you need a refresher of these error message, take a look through the error message cards again.
Date Functions
Very often dates and times are an important part of Excel data. Numbers that are correct today may not be accurate tomorrow. So, it is frequently useful to include dates and times on your spreadsheets.
These dates and times fall into two categories – ones that:
- Remain the same. For instance, if a spreadsheet includes data for May 15th, you don’t want the date to change each time you re-open the spreadsheet.
- Change to reflect the current date/time. When it is important to have the current date or time on a spreadsheet, you want Excel to update the information regularly.
Take a look at the list of Date and Time functions offered in the Function Library on the Formulas tab (see Figure 3.18).
For our gradebook, we want the date and time to be displayed in A2, and it needs to update whenever the workbook file is opened.
- Make A2 your active cell. Notice that A2 extends all the way from column A to Column R. Previously, someone has used the Merge & Center tool on this cell to make it match the title above.
- On the Formulas tab, in the Function Library, select NOW from the Date & Time drop-down list and then click OK.
Mac Users click the “Done” button in the “Formula Builder” pane at the right side of the Excel window; then close the pane. - The result you will see in the formula bar is: =NOW(). The result you will see in A2 depends on the current date and time. The NOW function is a very handy function. It takes no arguments and is Volatile! That is not as alarming as it may seem. This just means that you don’t need to give it any more information to do its job and that your results will change frequently. You can update the date and time whenever you want – you don’t have to wait until you open the workbook again.
- Make sure that A1 is your active cell and press the F9 function key (along the top of your keyboard.) The time will update.
Excel will update this field independently whenever you save and re-open the file, or print it. It may happen more frequently than that – depending on how you have set this up in your installation of Excel.
Another variation of the current date is the TODAY function. Let’s try that one next.
- Make sure A2 is your active cell. Press Delete to remove the NOW function.
- From the Date & Time drop-down list in the Function Library on the Formulas tab (see Figure 3.18), select TODAY and then click OK.
Mac Users click the “Done” button in the “Formula Builder” pane; then close the pane. - The result you will see in the formula bar is: =TODAY(). The result you will see in A2 depends on the current date. Since we haven’t asked for the time, the time you are seeing is likely 12:00. That is not very helpful so we need to change the format of the date.
- On the Home tab, in the Number group, press the Number Format Launcher button (see Figure 3.19).
- In the Format Cells dialog box, click the Number tab. Choose the Date category and select Wednesday, March 14, 2012 (this format is called Long Date).
Mac Users: there is no Number Format Launcher button or “Format Cells” dialog box.
Click the list arrow next to “Date” and choose “Long Date” - The current day and date will display in A2.
Keyboard Shortcuts
Sometimes you want the date or the time to show up in your spreadsheet, but you don’t want it to change. You can simply type in the date or time. Or, you can use shortcut keys.
- CTRL ; (semi colon) will bring you the current date
Mac Users: same as above
- CTRL : (colon or CTRL SHIFT ; ) will bring you the current time.
Mac Users: SHIFT COMMAND :
Key Takeaways
- Functions don’t always have to be about arithmetic. Excel provides functions that will help you perform logical evaluations, look things up, and work with dates and times.
- Excel displays error messages when your formulas and functions are not constructed properly.
Attribution
Adapted from Beginning Excel 2019 and licensed under CC BY.