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
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: vLookUP and the Logical Function IF. This will be fun.
Create the Lookup Table in Excel
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.
My Approach
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.
Can these formulas be used with the other Logical Options: And, Or, Not? Absolutely. But that is new story for another day.
