[MUSIC] The VLookup is one of the core functions in Excel, and one of the most frequently used. The VLookup allows you to look up a value in a data table and pull a specific attribute corresponding to that value, to a new table. For example, let's say I have a data set with a name on it. Now imagine I also have a data set that started with the same name, followed by a series of attributes related to that name. VLookup is going to allow me to lookup the name in the second data set, and then select which attribute or attributes I'm interested in pulling into my new data set. VLookup's work best in structured data sets, and your lookups range always needs to be all the way to the left of the table. The V in VLookups stands for vertical. There's a separate formula called HLookup which stand for horizontal lookup. Which allows you to lookup the data horizontally. Where the vertical lookup, the VLookup, looks vertically across the selection. Generally, we see VLookups more frequently than HLookups due to the way data is normally structured. However, HLookup is a good tool to have in your pocket. The syntax for VLookup is fairly straightforward. It starts with a lookup value. Essentially, what am I trying to lookup? The next input is the table array. Where am I trying to lookup this information? And next, is the column index number. How many columns from the matching value do I want to look over and bring into my data set? The last piece in brackets is an optional field called the range_lookup. This is asking whether I want an exact match, or a partial match. Most of the time we want an exact match, but it is a leading practice to specify true or false, something we need to be sensitive to as we go forward. Some other things to keep in mind are the VLookup is always going to take the first match that it finds in the data set. It stops looking once it finds a corresponding match, even though there may be multiple matches further down. Second, if Excel cannot find a match, it is going to come back with an error. We will learn how to deal with both of these problems as we become more familiar and we practice using the VLookup. So, let's now shift actually playing with VLookup within Excel. Why don't we start with basic VLookup problem? First, let's get familiar with our data set, and the question we are being asked to answer. Over here we have a very basic table, starting with distributor ID, the distributor country, and the distributor name. Down here we have another table with just the distributor ID. The first question asks us to match the distributor's name to the distributor's ID. This is a perfect use case for a VLookup. So why don't we get started. To begin we are going to move into the cell where we want to work. And as discussed prior, we are going to start writing our formula plucketing the equals key. Now we are going to start typing the name of the formula which is VLookup. I am going to hit the tab key which will fill out the rest of the formula. You will notice that within the VLookup formula, the lookup value is folded. This allows me to know which section of the formula I'm working in. The view with the value I want is the distributor ID, since I'm trying to pull in corresponding information with the distributor ID being the common key. I'm going to select 23265, the first distributor ID, and hit comma. Next, I'm going to select where I want Excel to look up this information. I'm going to select my entire data table from the top left to the bottom right. As discussed in the basic functionality section we're going to want to lock these cells so it's easier to copy the formula. If we are copying and pasting this formula, we'd like to keep the data table consistent, so I'm going to hit the F4 key to get the $ sign next to both the row reference and the column reference. Next, I'm going to click comma, and it's asking me to insert what column index number I am looking for. In other words, how many columns over is the data that I want to pull into my table? Starting with the data that was being lookedup. The distributor ID will be in column one. The country in column two and the name in column three. For this example I want column three as that is where the name resides. Now, I'm going to hit comma to move to the next piece of this syntax, which is range_lookup. The range_lookup is an approximate match or an exact match. The best practice is generally doing an exact match. So I'm going to type false and then close the formula. You will see that the answer is Uriel Benton after I hit Enter. Note, this matches a name for the distributor ID in B21. Since we properly locked and anchored the formula, we can copy and paste down the formula to get the matching distributor to 23315. Let's look to see what happens when we copy and paste. We will see Anika Tillman appeared for distributor ID 23315. [MUSIC]
