Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
In Excel for Mac 2011 Essential Training, author Curt Frye gives a comprehensive overview of Excel, the full-featured spreadsheet software from Microsoft. The course covers key skills such as manipulating workbook and cell data, using functions, automating actions, printing worksheets, and collaborating with others. Exercise files accompany the course.
When you maintain lists of data in Excel, you'll find it useful to be able to look up values in those lists. Rather than page through your data by hand or use the find feature, you can create formulas that enable you to enter a lookup value in a cell and have Excel return related information from a list or an Excel table. The lookup formula that you use in a table depends on whether you're data is laid out by column, as this is, or if it's laid out by row. If it's laid out by column, you use something called a VLOOKUP - the V stands for vertical - or by row, in which case you'd use HLOOKUP - the H stands for horizontal.
VLOOKUP is by far the more common of the two, so I'm going to concentrate on that. If you want to learn more about HLOOKUP, then you can find it in the Help system. So my goal is to type a value into cell F1, in this case it will be a product code, one of these four codes in this example, and then have the formula in cell F2 use that value to look up the name of the product, or in this case the flavor, or the infusion of the olive oil that's made by the Two Trees Company. So to create this formula, type =vlookup(.
Once you do that, you can start entering in the formula's arguments. The lookup value is the value that Excel will check for in the first column of your table, in this case A4 through A7. We're going to type that product code here, in cell F1, so I'll type F1. Next I need to type in the table_array. The table_array is the table of data, or in this case the list, where Excel should look. That starts on A3 and ends on C7. Don't get confused and include a header. Make sure that it's only the actual data table down here.
So that goes from A3 to C7. I'll type that in A3: C7 and a comma, and now the index number. The index number is the table column from which you're going to look up the value. We have column one, which is the Product Number, and then column two, which contains the information we want, column number two, and then finally we have an optional argument called range_lookup. Basically what range_lookup does is ask whether or not Excel can have an approximate match. If it's an approximate match, then for example, if I were to type in P005, which is larger than the last value in the first column of the table, then it would say, well, P005 is larger than the last value, but I'm going to return the last value, because that's probably what was meant.
In this case I don't want to allow Excel to return an approximate match. I only want exact matches, so I get the exact product. So I'll type in False. If I typed in True or left that argument blank, then Excel would allow approximate matches, but because I'm typing in False, it won't. Type a right parenthesis, press Return and you see that I have an N/A, or not available error. The reason that occurs is because there is no value in cell F1, but if I type in P001 and press Return, then I get the value Lemon in cell F2 through the formula.
The reason that happens is because Excel looks in the table, looks in the first column and sees P001, which is the value in cell F1, and then it looks at column two and finds the cell in that row. That's Lemon, which is the value that's returned here. If I already change this value to P003, we would get Rosemary, which is here, and just for an example if I were to type in P005, which does not occur in the column and press Return, we get the same error again. Now I'll go into a little more detail and show you how to use approximate matches.
So let's go to our Discounts page, and the idea here is that we want to be able to calculate the quantity discount that we're going to give to our customers. If they order Quantity 1, no discount, if they order at least Quantity 10, 5% and other discounts based on other quantities. So let's now try to lookup the discounts for quantities that are entered into cell F1. So we use a VLOOKUP formula again, vlookup, left parenthesis. The lookup_value is in cell F1. The table_array goes from A3: B8, column index number, we're looking up our discounts - that will be column number 2. And we're allowing approximate matches, so that means that I can either leave the range_lookup argument blank, or as I prefer to do, I can type in True.
I usually type the range_lookup argument into the VLOOKUP formulas that I create, just so that I remember whether I'm allowing approximate matches or not. I can't always remember what the default is, so I put it in to make it explicit, so it's easy for me, and anyone else to remember. So my formula looks good. I will type a right parenthesis to close it, press Return, and we get the error, but as soon as I enter a quantity in cell F1, such as quantity 10 and press Return, then I see the discount of 5%. But what would happen if I were to enter say, 15? There is no 15 here in the Quantity column, but if I type the number 15 into cell F1, Excel uses its approximate matching capability and sees that even though there is no 15 here, 15 is greater than 10 but less than 100, so that means that it looks here and applies this Discount.
The same thing would occur if I were to type 1,500, which is greater than 1,000 but less than 10,000. Hit Return. And if I were to type 20,000, which is greater than the last entry of 10,000 and hit Return, then I still get the same discount. The only time that I would have an error allowing approximate matching is if the number is less than the lowest number in this column. Now you notice that the numbers in this column are sorted from lowest to highest; in other words, they are sorted in ascending order.
If you're going to use approximate matches, it is absolutely vital - actually required - that you sort your data in ascending order. If you don't, the formula will fail. So if I were to type in a zero, I'll once again get the N/A error, and that's because that number is lower than the lowest value in the list over here. The VLOOKUP function is extremely useful because it lets you use one value, such as the Product Number, to find another value, such as the product's name or price. It might seem a little complicated at first, but you'll find it comes in handy once you learn how to use it.
Find answers to the most frequently asked questions about Excel for Mac 2011 Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.