Master Microsoft Excel: Learn the Vlookup Fast!

1. Introduction - The Vlookup: Hello there. I'm Jeremy, or as some call me Big dog. Welcome to my Skillshare course on the VLookup. Before we jump into the content, let me take a second to introduce myself. I've been in the working world for almost seven years and all that time has been spent in Excel for my time in college to my current role today, Excel has been a part of my daily toolkit. I've developed a passion for analytics and interest in figuring out the most efficient solutions and a love for automation. My goal for this video series is to make you an expert on the VLOOKUP formula. And in record time, the VLookup allows you to combine multiple data sources in matter of minutes, being able to analyze from different systems, multiple users or across segments speeds up data analytics and allows you to be a productive Machine. By the end of this course, you'll be combining data that you never thought could be partnered up. Ladies and gentlemen, the VLOOKUP. 2. Vlookup Basics - Part 1: Hi everyone, Welcome to the second video series where we will learn about the VLOOKUP formula. The VLOOKUP allows us to aggregate common datasets within Excel sheets and separate workbooks. And it is a foundational building block to more complex formulas. The upcoming videos, I will walk through multiple examples of how to use the VLookup formula. And by the end of the series, you'll be a VLookup machine without further ado. Let's jump into the first example. In this example we have a working table and a lookup table. In the working table, we have a list of invoice totals from March, the customer that each invoice total belongs to the region of the US where each customer resides. In the lookup table, we have the different regions within the US and the account manager that is assigned to each of those regions. We would like to bring in the account manager from the lookup table into the working table through the region common ID. Well, there are other formulas that could help us with this task. The VLOOKUP is the best suited for the job. The VLOOKUP begins with the lookup value. The lookup value is the value that is common between the lookup table and the working table. One very important note here, the VLOOKUP will only work if the lookup value is included in both the lookup table and the working table. And the lookup table or table array has the lookup value in the first field or column. This is one of the most common mistakes that I see with this formula. In this case, the lookup value that is common between both the working table and the lookup table is the Region column. Lookup value will be B11. Next is the table array. The table array is the range of cells that includes both the lookup value data and our desired data. Also, just to reiterate, the first column in the table array must have the same lookup value data from the working table in it. If this is not true, the formula will not work. The table array in this example would be a3 through B6. And since we don't want the table array changing cell references as we copied the formula, we will need to make this range and absolute reference. The next part of the formula is the column index number. Each column of the table array that we just defined has a number associated with it. The first column of the table array that includes our lookup value or region data in this example is column one. The account manager information that we want to get is column to the column index number is asking for the number of the column in the table array that has our desired data. In this case, our desired data is in column two. So we would input two into the formula. The last part of the formula is the range lookup. The range lookup field allows us to enter either true or false. True represents a close match and false represents an exact match. To keep the formulas easier to understand, we will use false here and go over what true means in later examples. Let's copy this formula down to the other cells and check if we got our desired result. Bill will is the West region account manager. Jake lake is the north region Account Manager. And forests chorus is the East region account manager. Awesome. We're getting exactly what we expected. Let's move on to the next example. 3. Vlookup Basics - Part 2: Welcome to the second video in the series. In this video we'll be going over the second example for the VLOOKUP. If this is the first video that you're watching, I recommend that you go back and watch example one. The example one video goes into more detail on the basics of the VLOOKUP that will not be included in this video. Let's get right into it. Just like in the first video, we have a working table and a lookup table. In the working table we have a list of invoice totals from March. The customer that each invoice total belongs to, the region number that's associated with the region where each customer resides. In the lookup table, we have different regions within the US, the region number and the account manager that is assigned to each of those regions. In the previous video, we brought the account manager into the working table through the field common to both the lookup and working table, the region name. In this example, the working table only provides the region ID, so we'll have to use that as the common lookup value. The VLookup begins with the lookup value be ten. Next we define the table array C3 through C6. After that, we input the column index number, which unlike the previous example, is in column three instead of two as our lookup table has an additional column. Finally, we add in the range lookup, which will be false. Cool. Let's copy this down and see if we get our desired results. Interesting. The VLookup didn't work. It seems that every other cell is giving us an error. Now, obviously this was by design as I wanted to illustrate, a common error that the VLOOKUP runs into, and that is data-type. If we click into the BLM one cell, we notice that there's an error symbol that says that the Region number is stored as text. Now, like I mentioned in the previous video, in order for a VLOOKUP to work, the look of value in both the working table and the lookup table has to be exactly the same, including data-type. If for the errored out cells, we had Region numbers stored as text, we would be okay. But all the region numbers in our lookup table are stored as number datatypes. So we have to make are working table match. Let's embed a simple formula within our VLOOKUP formula. The number of value formula converts any cell data to a number datatype. If we use this on the lookup value input in our VLOOKUP formula. It will convert any numbers stored as text to number datatypes, which allows us to match the lookup tables region number datatype. Let's copy this corrected formula down. Awesome, we're no longer getting errors. Let's move on to the next example. 4. Vlookup with Concat Formula: Welcome to the third video in the series. Now that we've mastered the basics of the VLOOKUP, let's try to combine the VLOOKUP formula with another common useful formula, the concatenate function. The concatenate function is used to merge the data of two or more cells. Since the VLOOKUP requires a unique lookup value, which the concatenate function is very good at creating the VLOOKUP and concatenate functions and make a great pair. One note before we jump in, the concatenate formula was replaced by the CONCAT formula in 2019. So keep that in mind in case you are using an older version of Excel. All right, let's get into it. Just like in the previous lessons, we have a working table and a lookup table. In working table, we have a list of invoice totals from March. The customer that each invoice total belongs to, the region number and region butter that's associated with the region where each customer resides. In the lookup table, we have the concatenated or merged unique region id and the account manager associated with each unique region ID. In previous lessons, the unique lookup value was in one cell. In this lesson, we're going to have to merge two cells to create the unique lookup value. The VLookup always starts with the lookup value. The lookup value field is going to use the CONCAT formula, which accepts multiple cell inputs. We will be using two inputs, the region number cell B11 and the region lighter cell C11. Next we define the table array A3 through B6. After that, we input the column index number, which is two, because the data that we want to return is in the second column of the table array. Finally, we add in the range lookup, which will be false because we want an exact match, not a close match. Good, let's copy this down and see what we get. It looks like we're getting exactly what we intended. Awesome. Now, I just wanted to take a second look at the CONCAT formula on its own. Before we wrap up, I wanted to clarify what this formula does in case there's confusion in cell F2. Let's enter the CONCAT formula with B1 and C1 as inputs. We are getting one a as the output. In our VLOOKUP example, the CONCAT formula is doing the calculation first and then passing the value one to the VLOOKUP. The VLOOKUP is then using one as the lookup value to search the table array. Feel free to leave a question in the discussion board if this is still confusing. Since this is the last video in the course, I'd like to thank you all for your time. You've been great students and I wish you the best on your Excel journey. If you're ever feeling stuck, feel free to stop back and ask a question. The big dog will always be here to help.