Join Dennis Taylor for an in-depth discussion in this video Find approximate matches with VLOOKUP and HLOOKUP, part of Excel: Advanced Formulas and Functions.
- [Instructor] In this worksheet called approximate match it's in our chapter three LOOKUP functions file, we're about to use either the VLOOKUP, or the HLOOKUP function in its form called approximate match, so we're about to take these salaries in column C, and return the tax rate based on the information that we're seeing either in this vertical table here in columns G and H or in the horizontal table we see to the right up in rows two and three starting in column J. Both of these lists have the same information in them. I think it's apparent to most people this list here is easier to read. It's not just because it's closer to the data by the way, but just the way it's structured we don't have to scroll back and forth. Whenever you're setting up a list like this give it some thought. Most of the time a vertical table fits better on the excel screen. Therefore, we will be using the VLOOKUP function much more often than we will the HLOOKUP function. V for vertical H for horizontal, and the way both of these functions work is in the example that we're seeing here. The salary the first salary 64,000 ultimately will allow us to return not 3.5% but 3%. The logic of how VLOOKUP and HLOOKUP work is not to find the next highest value but to revert to the value just below this entry here, and that's 63,900 although closer to 65,000 is actually going to be focused on the 55,000 number we're going to be getting 3%. Let's use this with VLOOKUP. VLOOKUP as we're using it here for an approximate match has three arguments to it. Three parameters as some people call them. We're looking up this value C two comma. A table array refers to this list right here comma, and now we indicate the column, and it's not column H although literally it is, we put in the number two. Reading from left to right column G is the first column, column H is the second column, and we get our answer of 3%. That happened to be formatted earlier if they were not might look like this using the comma button, but you didn't click the percent button. Before copying this down the column we need to make sure this reference here stays constant becomes an absolute reference we'll highlight it, press the function key F4. I'll press Ctrl+Enter here to complete the entry, and prevent it from moving down to the next cell. Double-click the lower right hand corner all these appear to be correct. It's important in lists like this for you're using VLOOKUP as an approximate match that these be in ascending order. They don't have to be equally spaced. We must cover the lowest rate of course no salary be below zero so that's automatically covered. if there were a salary in the list that were 150,000, a 180,000 something like that it would revert to the highest number that we see in the list here 7.5 %. If these are not in ascending order you still might get a lot of believable incorrect answers. I'm going to change the 35,000 right here to be 85,000. As I press Enter keep an eye on column D, some of those rates will change, but a lot of them didn't and you can imagine in certain situations you're checking a list, or so that one's right okay, that one's right, but you could overlook the fact that some of these might not be. Always give this a quick look. For an approximate match VLOOKUP and for HLOOKUP these need to be in ascending order, and we're talking of course if we were using an HLOOKUP we're talking about the left-to-right order of the top row of that list. If we were locked into using HLOOKUP, because of existing formulas on a worksheet that we didn't want to disrupt, we have to use that other horizontal list. This would be HLOOKUP obviously the location of the table would be different, but the two would stay the same if this were an HLOOKUP it would refer to the second row in the table, that were over there that has the percents in it. Now another adjustment we could make here makes it a little bit simpler instead of using this which certainly is not wrong if you drag across columns G and H and then copy this down the column. This will work as well it's certainly less cluttered looking and the only caveat there is make sure no other information ever goes into columns G and H, so that will give us the same answer. I'll drag across just a few of these temporarily make this column a lot wider, and expose the formulas. We can do this with the Keystroke Shortcut Ctrl+Tilde, and we'll see the difference in the two sets there. And I think it's clear too as to what really is happening. I'll press Ctrl+Tilde again to go back to a normal view, so we've seen how to use the VLOOKUP function for an approximate match kind of a scenario here, and although we could have used HLOOKUP We strongly recommend VLOOKUP in most cases particularly because the table is much easier to read, and of course in both situations it's always easier to work with these functions if the table data, the table LOOLUP array is on the same sheet.
- Displaying and highlighting formulas
- Debugging formulas
- Creating 3D formulas
- Creating nested IF functions
- Exploring VLOOKUP, MATCH, and INDEX
- Tabulating data with COUNTIFS, SUMIFS, and AVERAGEIFS
- Finding values with statistical functions
- Adjusting results with rounding functions
- Converting values between measuring systems
- Calculating dates
- Returning reference data
- Manipulating text
- Extracting information