CS 100 (Learn) — CS 100 (Web) — Module 05
NOTE: If your internet access is restricted and you do not have access to YouTube, we have provided alternate video links.
Note: This video transcript has been slightly modified. Corrections are marked with strikethrough, and alternative wording has been placed in [square brackets] to correct some of the awkward or confusing phrasing in the videos.
In this video we are going to learn how to use the VLOOKUP function in Excel.
The VLOOKUP function is very useful for looking up data in a table.
The example we are going to use right now is: we have some provinces and what the sales tax rate is in that province (which includes federal tax for all you tax nerds).
The first thing I am going to do is name this table I am just going to call it "taxtable" to make my life a little easier.
Let's do a concrete example of how to use VLOOKUP: I am going to start typing a formula [and] the function VLOOKUP.
The first thing I want to know is what do I want to look up, so in this case I want to look up the Ontario tax rate.
[Next,] I specify where the table is. Fortunately, I have given it a nice name taxtable, and now I [need to specify] which column [I] want from this table.
It looks in the first column for a match... it is going to find Ontario in that table because Ontario exists... but then [it needs] to know which column [we] want to use. In this [example] I want the second column.
I give a value of two [for the] last [third] parameter.
You will notice [there] is a Boolean value [for the fourth parameter]: it either wants true for approximate match or false for exact match. I am going to specify false and we will explore this option later.
=VLOOKUP("ON",taxtable,2,FALSE)
That is everything we need to do: VLOOKUP is going to look that value up in the table and see that this Ontario sales tax rate is 13%.
If I specify a different value I could lookup Quebec (which has a very awkward sales tax rate and it says it is 15% -- it is not showing all the decimal places).
What if I try to look up my friend in Washington DC [who] wants to know what our sales tax is... it is going to give an error message that says not available: it could not find DC in that column.
We could use our fancy IFERROR function if we can't find the sales tax rate we will just say "not in table". this will be a little friendlier version of what it did before.
Let's [do] one more [example and] look up the sales tax rate in BC... we see that it is 12%.
Let's use this in a practical application. We are going to [revisit] our sales example where we have some random people from Canada and their orders.
We want to calculate how much tax is going to be on their order. I am going to use VLOOKUP: I am going to say: "look up that province in my tax table": I want column two and false for an exact match.
The tax is not quite what we want... we want to calculate the tax, so we are going to take that value and then multiply it by the order amount.
We can see how much tax is going to be on each of these orders. We have a column that generates the appropriate level of tax, based on the different provinces.
This is a very practical example of where you could use VLOOKUP in your spreadsheets.
Let's do one more example. [Consider that] we have a shipping amount as well. I have [modified] this table: it is not just for tax rates... I am also going to include how much it costs to ship to each province.
Let's say our company is based out of Ontario, so the shipping rate to Ontario is $5 but the rates to some of the other provinces are going to be higher so shipping to Alberta is $10, British Columbia is $12, Manitoba is going to be $8, Northwest Territories is going to be expensive, Quebec is going to be same as Manitoba and Saskatchewan the same as well.
Now we have extra shipping rates added to our taxtable. I am going to change the table (even though I call [it] taxtable)... what I am going to do is modify it so it now includes this extra column.
Now we can look up the shipping rate: I am going to do something very similar to what we did before I am going to VLOOKUP look up the province in my tax table and now I want column 3, so we now want the third column value, so that is going to be column 3. Again, I am going to use exact match here.
=VLOOKUP(C4,taxtable,3,false)
We can see this shipping rate (format that as a dollar amount) and then just copy that down to all of our customers.
Now we can see the different shipping rates based on the province that they are located in as well.
Just one more example... we are using a different column than we were using before, and in fact if we look at the formula, the only difference between this calculation is using column 3. [The previous example used] column 2.
For our final example, let's tackle what that fourth parameter is about: exact or in exact lookup.
Let's create a new sheet and what we want to do is we are going to have a discount rate based on a certain order size. If your order size is between zero and $500 you do not get a discount.
Let's [enter] this: discount is going to be zero percent. If [your order] is above $500 then you are going to get a discount of 2%, if your order is over $750 then your discount is going to be 3%, if it get orders over $1000 then you are going to get a discount of 5%.
We have different discount amounts based on how big your order is so.
What VLOOKUP does is it looks in the table and it finds the smallest [largest] value that still matches [is less than or equal to] what you are looking for.
Let's do some concrete examples. I am going to call this table discounts.
I VLOOKUP the value $10 in my discounts table, and I am looking [at] the second column and then I [specify] true [for] approximate match.
=VLOOKUP(10,discounts,2,true)
If my order is $10, my discount is zero.
What if my order is exactly is $499? My discount is still zero, but as soon as my order is $500, I am getting a 2% discount.
If I bump the order up to $750, it says my discount is now 3%.
I am going to enter the value $888... [which] does not appear in this table.
What Excel does is it finds the the row with the smallest [largest] value that corresponds to the value smaller than [less than or equal to] what you were looking [for].
750 is the smallest [largest] value in the table that is still less than or equal to $888, so it determines that your discount rate is 3% [... the value for $75].
This function was developed for doing this kind of a lookup... for thresholding different ranges and assigning different values to those ranges.
We can [add] this [to] our main spreadsheet.
Let's calculate our discount, and what I am going to do is VLOOKUP the order amount in my discount table, and I want the second column (which is going to be the actual range in this case). But, I am going to [specify] true [for] an approximate match.
Then my discount is going to be that amount [percentage]times the order itself (let's view that as a dollar) and we will calculate all the discounts that people are receiving.
We can calculate the final total: their order plus the tax plus shipping minus the discount they get and now we have [the] full calculation to determine [what] the final total amount is going to be for each customer.
[This has been] a practical example of how we could use VLOOKUP in a table.
There is also an HLOOKUP function that is very similar except it looks up across rows and then finds the corresponding column to match. With VLOOKUP, we look in the first column find the row we are interested in and then look up the appropriate column.
I just want to clarify one more time how the last parameter of VLOOKUP works: if the value is true then uses an inexact match... and I did not mention this earlier, but this is very important [when] using inexact match: the table must be sorted by the first column so the entries in the first column must be sorted from smallest to largest. They do not have to be numbers: they can be names and strings [or text] or dates, or something like that... but they must be sorted from smallest to largest.
If the fourth parameter is false, then it uses exact match and the table does not have to be sorted.
That is one last clarification on how to use VLOOKUP in Excel.