Join Dennis Taylor for an in-depth discussion in this video Getting approximate table data with the VLOOKUP function, part of Excel 2016 Essential Training.
- An important and powerful function in Excel is called VLOOKUP, like a V for vertical. There's a companion function called HLOOKUP. Think of H for horizontal. We're looking at a worksheet called VLOOKUP Approximate. There are two major kinds of VLOOKUP uses, one for approximate matches, one for exact matches. On this worksheet, we're about to put in a reduction percentage in column E. There's a table off to the right in columns H and I. It could be on another worksheet.
In our example, it's in the current worksheet. And by looking at it, you probably have an idea already as to what it's going to do. Based on the Subtotals that we see in column D, we want to calculate a reduction rate. The Subtotal for cell D2 is 1,363. Now different people might read this differently but the way the VLOOKUP function is going to work is it's going to be comparing that value with different values in the left column of this table. And if we have not reached this 1,500 level, then the percent below will apply, 3%.
So we're going to be reducing this amount by 3%. Now lookup tables, as this might be called, it's not an official name, can exist in various forms. They might have three columns, four columns, many columns. Sometimes tables are structured horizontally. Now I could take this data right here, copy it with ctrl +c, and down here, by using special right click, Paste Special, there's an option here called Transpose. If the data looked like this, we would be using a function called HLOOKUP, if we wanted to look up data in it.
If you're laying out a table for lookup purposes, it's probably more common to see data in columns. It tends to be more compact and it's easier to read. But again, nothing wrong with this other table. Now this function called VLOOKUP can also let us compare large lists with other large lists. So it's not exclusively designed to work just with small tables. But in our example, that's what we'll be using. By definition, a VLOOKUP function allows us to compare a value with the left column of a table.
And then, depending upon how that value falls into place, we can gather data from other columns in the table. And so ultimately what we're trying to get here is a 3% for this particular number. For the next number, as you look at the table, it has not reached the 2,500 level, therefore it would be 6%. So I'm going to use the VLOOKUP function here. It can be used in two major ways, as I've said. This is called an approximate lookup because the numbers we're looking at here do not have to match up exactly with these.
Now maybe one or two of them might, and that's okay. But they don't, for the most part, line up exactly with those numbers. I'm going to make column E wider. This is not truly necessary but as I'm typing in the function, I want it to be clear on the screen. And I'll zoom in a little bit in the lower right hand corner clicking the plus in the Zoom In slider bar. There we go, like that. And I also want to scroll a little bit to the left because we do need to see column D. When you use VLOOKUP for an approximate match, it has three parts to it, sometimes referred to as parameters or arguments.
Here is the amount we're looking up, comma. The location of the table. It's these cells over here, comma, Now which column of the table has the answer? If we're looking left to right and at the column letters, we'd say column I. But when we use this in the formula, we refer to the columns by number. The left column, column H, is the first column in this example. The second column is column I. We put in the number 2, not the letter I here.
It's the second column where we'll get our answer. And if this is going to work properly, we're expecting to see the percent here, 3.0%. By the way, if that were not formatted, we might see it as .03 and we could format that off the Home tab with a percent. This was done ahead of time that's why it appears that way. Now, if we were going to be copying this down the column, we need to make sure that our reference to these cells right here stays absolute. So double clicking back in cell E2, we want this reference here to be absolute, so we can highlight it and press F4, and then Enter.
Still the correct answer. The formula to the right by the way, you might not have seen it, takes the original Subtotal and then multiplies it by the difference of this percent from 100. So it's reducing it by 3.0%. In effect, this amount here in the example, is 97%. So 97% of 1,363.80 is 1,322. So we are reducing these based upon the amount and you see how the percentages change here.
If the amount is under 500, it will not change at all. So if we drag this down into the fourth row here, where we have the 337, 0.0%, we're not reducing this at all but the others we are. And we can quickly copy this down the column by using one of Excel's great shortcuts, double click. Copy this down the column. And we can scroll down to the bottom to see how that's working through all these if we wish. There's also a shortcut for that too. You can press ctrl + . that will take you down there and then back up top with ctrl + .
So this is an example of VLOOKUP being used as an approximate match. There's also one for an exact match. One other aspect about this that's important, the left-hand column of the table must be in ascending order. Now suppose we were not paying attention to this and maybe this entry here, by mistake, had been put in as 4,000. Now keep your eye on column E as I press Enter. Most of those percentages changed. Now occasionally, depending upon the magnitude of the change or where it occurs, you might not see any problem with the data, so you've really got to be attentive to this idea.
Always check this out. When you're using VLOOKUP for an approximate match, the left column of the table must be in ascending order. And you must cover the lowest possible entry. It's pretty obvious here we can't have anything below zero in these, so well that's covered. That's actually a zero. On the high end, if we have a Subtotal amount over there that's higher than our highest entry, it simply reverts to the 8.0% as we see it here. So to correct this here, I'm simply going to press ctrl + z. And again, keeping an eye on column E, we see how those are all adjusted.
Notice the entry over here for $4,700. That's well above the 2,500. We see that it's 8.0%. So we don't worry about the top end and the low end in this case, we're not worried about. But if we started this at 500, in other words, if this were the location of the table, an amount over there to the left, below 500 would turn up as an error. Right here, for example, we'd get that. So this is an example of using VLOOKUP for an approximate match lookup.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros