Vlookup Function Examples 1

Suzanne here. Welcome to Excel Intuition’s Vlookup() Examples 1 Video. I recommend you download your own copy of this companion workbook, so you can try things on your own, and examine all the contents. Let’s take a look at the definition of the Vlookup() Function.

The Vlookup() Function is a vertical lookup, meaning it looks for a match to the Lookup Value argument, in the First Column of the Table Array argument, and then, when it finds a match, it refers to the Column Index number given, and in the row where the match is found, it returns the value from the column indicated by the Column Index number.

The Range Lookup argument is set to FALSE if we want to do an exact match lookup. It’s either omitted, or set to TRUE if we want to do a range lookup.

Well let’s take a look at a table array, and see what it would look like if we were doing the lookup. So here’s the Sales Tax Rates for cities in Monterey County, with over 5 000 in population.

If the Excel

Operator is using the Lookup Value in the Vlookup() Function for searching in this table array, it’s going to search in the first column. The first column of the table array, is where the match is looked for.

So, it would find a match for the city, and then it would need to have been given the Column Index 2, to return the correct sales tax rate for that city.

So let’s take a look at where we have this set up. We have a mini report comparing sales tax rates from two different Monterey County cities.

And our Vlookup() Function has the Lookup Value, is the value in B7, for this function, and that is the name of the city. The Sales Tax Table Array is that same table we just looked at, the Column Index number given is column 2,

and the Range Lookup argument is FALSE, which means we want an exact match lookup. So if we were using the contents of this function and we were looking at this Table Array, in this worksheet, we’d be taking the value of “Castroville” and we’d be searching down: “Is this a match?”, “Is this a match?”, “Is this a match?” We wouldn’t find a match ’til we got all the way down to this row with “Castroville” in it. And only if they were spelled exactly the same, with no hidden characters in either one, that could make them different, would there be a match. If there’s a match, then, because the column index number was 2, the value in the second column over from the far left column in the table, is the value that would be returned as a result of the function, if we were doing an exact match lookup.

So, a really key thing to remember: the Lookup Value is something you can SEE on the same worksheet in which you’re putting your function. So we should be able to click on that value, and we can, it’s in B7. Then, we need to know that our table array is set up so that this should be able to be found in the first column of this table array, if it exists, if a match exists. If no match exists, and we’re looking for an exact match, then we should get an error. And then, this Column Index that we give it, as the third argument, must be a number in relation to the column, the FAR LEFT column of the Table Array, where we look, where we expect to find a match. So that far left column is always going to be column one, and we count over one from there to find the Sales Tax Rate, so that’s column 2 in that table.

And that’s the end of our example using an EXACT MATCH lookup. Now let’s look at a RANGE lookup, The Vlookup() example two: we just have a very small table here: these are poverty level ratings.

We have either 0 as the

lowest value in the table, or 11.6% as the

result from the 2021 census. This was the the percent of persons in the U.S at the poverty level.

So we have a mini report over here, where we’re looking up the Persons in Poverty Percent, the percent of persons in poverty, in these different communities in Monterey County, according to the U.S census results of 2021. And we want to get a rating for that level, the rating, we just want it to be either “Below U.S Persons in Poverty Percent” or “Greater Than or Equal to U.S Persons in Poverty Percent.”

So here we want a RANGE match. This table is the Poverty Level Table Array. It has two rows and two columns each. Zero is the lowest possible value, and 11.6% is the Percent of Persons in the U.S considered to be at the Poverty Level as a result of the 2021 census. The way a RANGE match works is: Excel takes the Lookup Value and it compares it to the far left value in the table array, and if it’s greater than or equal to that value, it goes on to the next row. If it’s greater than or equal to that value, it would go on to the next row, if there was one, but if there isn’t it will stop there. So in this case, there’s only two values. It’s either going to be “Below the US Persons in Poverty Percent” or “Greater Than or Equal to US Person’s in Poverty Percent”

So we can see the Vlookup() Function in column 1, uses the Lookup Value of B9, which is the Persons in Poverty Percent for King City, and it checks the table. It’s greater than or equal to zero and it’s also greater than or equal to 11.6%, so it would stop here, because it can’t go any further. And then it would return the value in column 2, which is this text string:

“Greater Than or Equal to U.S Persons in Poverty Percent.” If we go to the second column in our mini report we’re taking this value of 8.1% as the Lookup Value, from C9, and it’s greater than or equal to zero, but it’s not greater than or equal to 11.6%. So, Excel will go back to this row, and say, this is my RANGE match, and it’ll return the value in column 2, which says “Below US Persons in Poverty Percent.” You can play around with this on your own, and see how the ratings change, depending on the percents that come up. Thank You.