Microsoft Office is most effective when the tasks are strung together in a sequence. It is how we process our work: many little steps one after the other. Today’s post presents the Dynamic Duo: vLook and the Logical Function IF. This will be fun.
As a database designer, I think of Excel as a “Prequel” to Access. The data in Excel and Access is the same, they can be linked. So the data should be normalized in both. There should be a Table for each type of information. In this example, there will be a Table for the Bonus (VLOOKUP). Then we can look up the right answer in the Tables.
Create the Lookup Table in Excel
Here is a YouTube video that demonstrates all of these steps that you can watch if you wish:
Intermediate Excel: Using Lookup Tables
The Lookup Function
Say your company offers a bonus for meeting sales goals. The bonus will be calculated as a percent of the sales. The best way to calculate the bonus is to look up the answer in a Table. Excel calls this the Lookup function. A vertical, or VLookup uses the values in the columns. A horizontal, or HLookup, uses the data in rows.
Before You Begin: Set up the Spreadsheet
Open a new spreadsheet in Microsoft Excel.
Add the following labels:
In cell C1, type Commission
In cell D1, type Bonus
In cell F1, type Sales
In cell G1, type Percent
Format the Labels Bold.
Format Column F for Accounting.
Format Column G for Percentage.
Enter the following sample data…
1. Try it: Create a VLookup Table
This Lookup table has two columns: Sales and Percent. Sales, Column F, is formatted for Accounting ($) and Percent, Column G, is formatted for Percentage (%).
2. Try it: Add data to the table
Enter the following values:
Keep going, please…
Name That Tune
In Excel, you can name a cell, or a range of cells. Using names makes it easy to go to a particular place. It also simplifies cell references when you create equations.
3. Try it: Name the Range
Select Cells F1 through G5.
Go to Formula->Defined Names.
Select Define Name.
What Do You See? The New Name screen will pop up. The Name, Sales, came from the label in Cell F1.
Refers to: Show the name of the spreadsheet, Bonus. The Range of data can be found in cell F1 through G5.
Click OK and continue…
4. Try This: Insert a VLookup Function
Select Cell C2.
Go to Formula -> Function Library.
Go to Lookup & Reference.
Select VLookup from the function list.
Please keep going…
5. What Do You See? Excel will prompt you to fill in the Function Arguments.
Here are some answers.
Lookup_Value: The first argument asks, “Where is the data?” In our example, Alex’s total is in cell B2. Click on cell B2.
Table_array: The second argument wants to know, “Where is the lookup table?” You can type the name, sales, for the range or use the red, white and blue lookup button to go to highlight cells F1 through G5.
Col_index_num: The third argument needs to identify where the answers are. In our two column Sales array, the percents are located in Column 2.
Working with Logical Formulas
The previous steps demonstrated how to create, name and use Lookup tables. Another useful set of Functions are the Logical formulas.
A Logical formula is binary. There are only two answers: True/False, Yes/No, Above/Below. It begins with a Logical Test. Say you wanted to calculate if the goals were met? For example, did sales exceed our goal of 15% ? You could use a Logical equation here.
1. Try it: Create a Logical Formula
Select Cell D1 and type: Goals
Select Cell D1 and format the label BOLD.
Select Cell D2.
Go to Formulas->Function Library ->Logical.
Click on IF
What Do You See? Microsoft Excel will prompt you to fill in the Arguments.
Logical Test: C2>0.10
Value_if_true: Well Done
Value_if_false: Needs Work
Please click OK.
OK, Have Fun with Conditional Formatting!
You can use Conditional Formatting to Highlight the results, based on TEXT:
If the text is Well Done: Green is good
If the test is Needs Work: Yellow mean caution.
Can these formulas be used with the other Logical Options: And, Or, Not? Absolutely. But that is new story for another day.
Good question. Thank you to the people who invited me to post an answer. Please let me know if I answered your question.
Elizabeth Nofs, the Computer Mama
You are invited to evaluate my NEW COURSE: Top Ten Tools in Microsoft Excel.
Here are the steps: Sign into the school. Then enroll in the course.
(Two clicks, two emails.)
FREE Our promise: No Cost. No ankle biters.