Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
Like the other applications in Microsoft Office 2007, Excel 2007 boasts upgraded features and a brand-new look. In Excel 2007 Essential Training , instructor Lorna A. Daly introduces the new version in detail. The training begins with the essentials of using the program, including how and why to use a spreadsheet, how to set up and modify worksheets, and how to import and export data. Lorna then moves on to teach more advanced features, such as working with functions and macros. Exercise files accompany the tutorials.
Let's say you wanted to look up the current cost of an item based on the Item# in our inventory list. You can look up the data quickly and efficiently, find a specific data in a list, and automatically verify that you're using the correct data by using the Lookup feature. After you've looked up the data, you can perform calculations or display the results with the values that are returned. Now there's several ways to look up values in a list of data and display the results. Since this is an essential basics video, we're going to learn about the easiest type of lookups.
And this is the VLookup. What we're going to be doing here is we're going to be looking up information in columns. Open up Inventory list 19. And this is the end result that we're heading for, so that we can see what we want to come up with. We now have a new sub table on the worksheet, that identifies the item number and its current cost. Right now, the item number is 14 and the current cost is $7.00. Change the item number to 17, hit your Enter key, and see what happens to your current cost.
The current cost now changes to $2.25. Let's see if that's right. We'll go over to our table, go to item number 17, look across the row to the current cost column, and we see that it is $2.25. Cool isn't it? Imagine how quick it will be to find the current cost of items in a large piece of data with this kind functionality? Let's learn how to create this table. So we're working in list number 19. To begin, if you haven't already opened it, please do so.
You'll notice that I've moved the Item# to be in column A, and this is important because the VLookup function searches on the first column in the range of items you're looking for. And since I want to look up an item number, I wanted that to be the first column in my list. Let's select cell J8. Go up to your Function Library from your Formulas tab, and this one is under the Lookup & References category. Select that. And you're VLookup function is at the very bottom. It's down at the very last function you can select.
So if we look for the definition of what VLookup is all about, it actually looks up values in the leftmost column of the table, and then returns the value in the same row from a column that you specify. So it's going to look up information in the first column, you're going to specify what you're looking for, in this case our Item#, and then you're going to say, "Go to a particular column and grab out the value that's sitting there, based on what I'm putting in." So let's look at the Function Arguments that can be created for this particular function and see how we set this up.
The Lookup value is the value to be found in the first column of your table. And it can be a value, a reference, or a text string. Now in this case, I want to be able to change up the number that I in here. I don't want to say, "Always show me what's in Item# 7." I want to be able to have the power to change my numbers here like we did in our example, and get a different current costs. So I'm going to put in a reference. So the reference that I'm putting in is going to be the cell that's right beside cell J8, and it's going to be cell I8.
We're going to start putting our item numbers just in the cell that I'm hovering over here in the future, and its going to allow us to change and find different current costs for different Item#'s. Next, the table array. This is a table of text numbers or logical values in which the data is retrieved. So this is now the area that we want Excel to go and look the information up in. in this case, it's going to be our whole table. We're going to look up and input the cell addresses for the table that we're working in. So we're going to put in cell A2 to be the very beginning table.
Address, a colon like we did in the last example, and F25 as our final cell that we want it to go look for. The next item we're going to identify is the column index number. And this is the column number in the table from which the matching value should be returned. In this time, we're not looking at A, B, C, D to identify, we're looking at a column number. So that we're starting off with 1, 2, 3, 4, 5, 6. We want to come back with the current cost, and the current cost is in 1, 2, 3, the fourth column.
So I put in the number 4, to identify I want it to go into column D. The final identifier is the range lookup, it's a logical value so by this we mean it's either true or false. And we want to identify whether or not we want it to match exactly, or if we just want to find the closest match. In this case, we want it to match exactly. So we want it to go and look for exactly item number 19. In order to do that, to find an exact match, we have to put in the value of false. Now this may have seemed a little bit confusing to you, but you are talking to a machine, so this is what the machine will understand; to make sure that you are going to find an exact match. Should you forget this when you are working with this particular function, that is also reminded to you down here, in this little dialog box.
So we now have our formula in place, let's see if it's bringing back the values that we're expecting. When we first add in the formula, we get an N/A over here that looks like we might have created an error in our formula, but basically the problem is, is that we have no value here in this particular cell, and it's looking for something to go search on. It can't look of nothing. So let's remedy this by clicking on cell I8, and putting in a number. Let's look up Item# 21.
Enter it by using your check mark. And now we have a value here in our cell, where we were putting our formula previously. So it's telling us that for item 21, that the current cost is $6.25. Let's see if that's right. We'll go down to our Item# column, we'll search for item 21, we'll use our arrow key to move across to our current cost, and we see that our current cost is indeed $6.25.
Let's do that one more time, because this is a pretty cool concept we're learning here. We'll go back up to our Item# cell, we'll lookup item number 13 this time, click our check box to enter the information. It shows us that it's $5.25. we'll verify that that's correct. And indeed it is. You should feel very proud of yourself. If you've made it through this movie and got this function to work, this is pretty advanced stuff. Now you're ready for more challenges.
Let's look at What-If analyses.
Find answers to the most frequently asked questions about Excel 2007 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.