Fine-tune your spreadsheet using lookup formulas
Learn how to use lookup formulas in Microsoft Excel 2010
Introduction (00:04)
Welcome to Business Productivity – I’m Ulrika Hedlund. Sometimes when you are filling out a budget, calculating salary payments or forecasting sales you need to base your numbers on a certain criteria. Let’s take the salary example. The amount you pay your employees might depend on how well they have performed. If they have performed exceptionally well they get a bonus, if not, they just get their basic salary.
In this video I’ll show you how you can use lookup functions in Microsoft Excel 2010 to fine-tune a marketing budget. I’ll show you how you can use different types of lookup functions, I’ll show you how you can name your cells to reference them and also how you can get a better overview of all the formulas in your spreadsheet. Let’s me show you!
Showing formulas in a spreadsheet (00:54)
Here I have a marketing budget that I’ve started to fill out.
Some of these numbers I’ve added manually, but some of them are calculated using formulas. To get a good overview of all the formulas in a spreadsheet you can go to the “Formulas” tab and in the “Formula Auditing” section click “Show Formulas”.
Now all the formulas in the spreadsheet become visible. You can also access this by pressing the keyboard shortcut CTRL + Accent Grave or CTRL + Tilde if you have a US keyboard. To go back to the normal view again I’ll click “Show Formulas”. Another way to get a good overview of the formulas in your spreadsheet is by highlighting those cells. You can do that by going to the “Home” tab, and in the “Editing” section click “Find & Select” and then in the drop down select “Formulas”.
All the cells with formulas will be selected.
Using the “IF” function (01:53)
Now I’m going to continue filling out this budget, so I’ll go to the first empty line item which is Direct Marketing.
Here I want to allocate a fixed monthly amount of 250 dollars but the months where we are investing a lot of money on events, 10,000 dollars or more, I want us to double that amount and make it 500 dollars. Instead of manually tracking which months have 10,000 dollars or more budgeted for events I’m going to use a lookup formula which will do this for me. I’ll start by entering an equal sign to start the formula, then I will write IF, left parenthesis and here I can see that Excel helps me with the formula.
First I have to enter the so called “logical test”, so I’ll say, if the budgeted cost for the event that I have for January in cell C14 is larger (>) or equal (=) to 10,000, comma, then the direct marketing budget should be 500 so I’ll type 500 and then a comma again, otherwise it should be 250. I’ll complete the formula with a right parenthesis.
There! Now, let’s copy this formula to all the cells to see if we get the right result. That looks right. The months where we have the events that cost ten thousand or more we spend 500 dollars in direct marketing, otherwise we spend 250.
Using formulas in your IF statement (03:29)
For email marketing we have a fixed monthly fee of 200 dollars, but the months where we have events that cost 10,000 or more I want to add an additional 1 percent of the event cost to the 200 dollars. So to do that I need to add some more calculations to my IF statement.
I’ll start with an equal sign, type IF again and left parenthesis, for the logical test I’ll first mark the cell containing the Events cost for January; C14, if this is larger (>) or equal (=) to 10,000 dollars, comma, if this is true I’ll add 200 dollars, plus (+) 1 percent times (*) the event cost, comma otherwise I’ll just add 200 dollars. I’ll end with a right parenthesis and then “Enter”.
Now, I’ll copy this formula again across all the months. You can see that I now have more money allocated for email marketing each quarter when I have my large events.
Make your formulas easier to read with named cells (04:38)
If you use a value over and over again, or if you want to test different scenarios by changing values, or if you just want to make your formulas easier to read, you can give your cells a name. Let’s change the email marketing formula and make use of named cells instead.
First I’ll type “Email Marketing monthly fixed” and enter the 200 dollars which is the fixed cost every month in the cell to the right. Then I’ll write “Email Marketing event increase” and enter the 1%.
Instead of referring to these cells as C23 and C24 I will name them. There are multiple ways in which you can name your cells. You can mark the cell and type in a name in the text box up here, I’ll click ESC to show you another way.
If you go to the “Formulas” tab and click “Define Name” you can enter the name here in this text box.
I’ll click Cancel and show you the third way. Mark your cells, the ones with the names and the ones with the values and click “Create from Selection.”
Here you need to tell Excel which cells you want to use for the name, and in our case its’ the cells to the left, and then Excel creates the names for me. If you click “Name Manager” you can see all the names you have in your spreadsheet.
Here you can add new names, or change and delete existing names in your spreadsheet.
Use cell names in your formulas (06:14)
So let’s go back to our formula again and use these cell names instead of numbers. As I start typing the name you will see all the matching names appear in the drop down box.
Here I want to select the fixed fee which is the bottom one so I’ll press arrow down on my keyboard and then press TAB to select the highlighted option. Then I’ll add the percentage increase and multiplied with the amount budgeted for the event. I’ll add a comma and then enter the name for the fixed fee. I’ll end with a right parenthesis and press Enter.
Now I’ll copy this updated formula across all months.
Now I can easily try different scenarios. Say for instance that I want to increase the fixed amount to 250 dollars and the percentage increase to 3%. I can see the results in my budget right away.
Using the VLOOKUP formula to look up values (07:10)
Next I want to budget for our PR. Our PR agency charges different fees depending on their media outreach performance. If their performance is 5, the cost is 5000 dollars a month, if it’s 4 it’s 3000 and so on. For budgeting purposes I’ll just assume that they will have the same performance this year as last. Now I need a formula that can check the performance number for the month, look it up in this table and give the value in this column.
I’ll go to my budget and enter the formula: Equal sign (=) and then I’ll start typing VLOOKUP and select it in the drop down list by pressing TAB on the keyboard. First is the “lookup_value”, this is the value I want to look up in the table, so I’ll go back to my PR sheet and mark the first cell for January
and then I’ll press comma, to enter the “table_array”, here I need to mark the columns in my lookup table with values, I won’t include the headers, here I’ll press F4 to make the table absolute, meaning that if I copy the formula it should always refer back to these exact cells for the lookup table.
I’ll press comma again to get to the next value which is “column_index_number”, this is the number of the column of your lookup table that has the value that should be returned, in our case it’s the 2nd column that contains the values. Finally I’ll press comma again and here I can select TRUE or FALSE.
False means that the numbers are exact, meaning that there is no performance of 4.5.
OK, let’s try this. The first result is 5000 which is correct, now I’ll just copy the formula across all months. There! It worked! Now we are done fine-tuning our budget!
Closing (09:11)
You probably won’t need to use lookup formulas very often, but when you are faced with a scenario it’s extremely time consuming if you try to do it manually. By just knowing that the capability exists you can always refer back to this video in the future if you need to use lookup formulas.
I’m Ulrika Hedlund for Business Productivity – thanks for watching!