Transcripts
1. Intro: Why Learn Lookup & Reference Functions?: In this class we're going to cover Lookup and Reference functions in Microsoft Excel. Lookup and Reference functions are in my opinion, some of the most powerful and useful functions in Excel. And learning them will help you analyze data with more depth, precision, and speed. They allow you to search, join, and find information about data. And this makes them useful for tasks such as combining datasets, which reading information about data, finding missing data values, and more as this a useful when analyzing when working with data, many employers will even request knowledge of certain lookup functions and job specifications, and knowing how to use them will be useful for a range of tasks. This course will teach you how to use the most common and useful Lookup and Reference functions such as VLookup, HLookup annex lookup, as well as showing you best practices for using them. Example, use cases, strengths and weaknesses of different functions, and how to avoid common mistakes and errors. And once you finish the course, you can test what you've learned with the class project. So function rolling in this class and let's get started.
2. Getting Started with the VLOOKUP Function: In this video, we're going to look at the VLookup function. Now the VLookup function is a really useful, really powerful function to know and understand. It can seem a little bit difficult to first and a bit confusing, but it's actually more straightforward than my same. So what it does is it looks in a range of data for a value in the leftmost column. And then it returns a value in the same row from a column to the right that you've specified. Now, that can sound quite confusing, but once we dive into this example, see it is actually quite straightforward. So we've got some fictional sales data here. It's a business that's got stores in various different locations. And then we've got the Q1 sales numbers in column B, Q2 sales and column C, Q3 and column D, and k4 in column E. Now, there's quite a lot of data here. You can see this whole range goes down to row 139. And then obviously with the four quarters, it's a lot of information. Now, what we've got up here is three Georgia stalls. And let's say we want to return the q3 sales values. We're just interested in the Georgia stores and we're just interested in the values for q three. Now we could find this information by making a table or filter here and then manually filtering and finding them or even sort of manually searching through and looking for those stores. But that's obviously not very efficient. And that's where the VLookup formula comes in here. So we start off writing up a formula so equals v lookup. And first off, you need to specify the lookup value, and that's simply the store in this case. So we're just going to select that Silva cell G2. Next up you specify the table array. Now this is where all of your data is. Now, we could specify from cell A1 and go all the way across here. Because this will encompass all four, all four of the sales columns and the store location and obviously would need to go to the bottom there as well. Or we could just specify from this cell here where the actual data starts. So let's just do that for now and we'll go all the way across here. But yeah, you don't as long as your including the column with the lookup value in and the column of the data that you're wishing to return. That's all that really matters. So we could just select the cells and go down there. Will, will go across to Q4. Now we'll select all the cells. Now, you can see we've obviously got this whole range selected. Now. Now I'm going to lock these in place, which we can do that with F4. And it just puts those dollar signs around this, which means when we move this formula around, the stem, cells will stay in exact same place, which is important because we don't want this range to move. Next up, we specify the column index number. So we're interested in Cuba, because now you have to count from left to right, starting with column where you look up value is, so this is 1234, so it's column number four. So we're just gonna specify four there. And then next up, we specify where we want an approximate match or an exact match in its true for approximate or folks for exact. Now, we want to match the lookup value's exactly. So we're gonna go with folks and enter that. And then we'll just close this off and then hit enter. And then you see here we have our value of 1.4.3, one T6 returned in the cell. So that's looked down this column here. It's then found lambda, which can be found all the way down at the bottom here. And then it's gone 123, and we've got 143126. And then again we can see that's the same value there. So it's worked just fine. Now we can then drag this down and we've instantly populated all three of the cells with the q3 sales figure for these three stores. And then we just check another one of those, or gusto, for example. So we've got 610104. We can see we've got August the hair, and that's 61010 for now, we could also the, change this formula. So let say we want to introduce Q4 sales into the cells rather than Q3 cells. Now, we can do that because we've specified the table array, we've specified all the way along to Q4. So what we can do is simply just changed this four to a five. Hit Enter. And then I'm just going to drag this to replace those two down there as well. And then we've got one to 8171 for Atlanta selects. Just check that and I'll just change that to q four sales. So if we scroll back down to Atlanta, we can see Atlanta Here it is, one to 8171. So you can see it's pulling in the correct number there. So that is the VLookup function just with one example. As you can see, it's really quite powerful and it's not actually that complicated overall.
3. Ensuring Table Arrays are Locked: In this video, we're going to look at what can happen when you do the lookup function, but you don't lock the table array section of the formula. So we've got three stores here, Augustus Portland and to leader. And let's say we're interested in the Q2 sales for these three stores. So we'll start off with our VLookup function and then just select our lookup value. And then we're going to specify the table array. And we're just going to go from column a, two, column C, because we are only interested in Q2 sales, so we don't need to go all the way over. And then we're going to select down, select all of these cells. And with the table array, we're just gonna leave it with dollar signs, so not locked. And just as an example, and now we're going to specify the column index number. Now this is going to be three because we need to go 123, cost Q2 sales to specify three there. And then again for range_lookup fos close off this formula. And then we're going to drag this down. And what you'll notice is that in the cell for Toledo QT cells we get an NA value. Now why is that? Toledo? It is over here. So it is in the cells that with four we will kind of specifying. However, when the formula was dragged down, this range moves down because we haven't locked it. So here it's gone from a to C 139. And then here it's a three to see 140. So you can see it's down one. And then on this one here where we need the Toledo Valley to be included to lead those hair. So it's excluded from the table array. So the formula is looking for this value to lead out in all of these cells. And it can't find it because it goes down to here. But these are end-use. We need our table array to be locked on these cells here. So we can fix this if we just go in and unlock this. So we'll just lock that. I hit enter and then we'll drag this down. And then, because our table arise now locked so you can see it stayed in the same place. So it's working fine against Toledo q2 cells 97, 1100. And then we see that 971100 for Q2. So it's working fine now, but that just exemplifies why it's good practice to always lock your table array. And the problem that can occur when he died.
4. Ensuring Consistencies with Lookup Values: In this video, we're gonna look at what can happen when you're lookup value has a space in the cell either at the beginning or the end. And the era that, that can create when this space isn't in the same cells matching in your table array. So we've got our three stores here from Georgia, Atlanta, Augustine, and Columbus, and which can enter in a VLookup formula here to get the values from our table for Q3 sales. So I'm going to select these cells, lock that for folks. And then we're going to drag this formula down. And what you'll notice is that we get an NA value here and error. Now, August the, we do have the August the store in this list serve we see we can say here, so we should be getting the value of 610104, but we're not. And that's because just to exemplify the Zara, I've added a space here. So we've got a space at the end of the word Augusta. And if we compare that with this cell here, there's no space. So you can see we're right up against the Ada. And that means that these two cells don't match. So if we were to remove that space, the VLookup works fine and it pulls in the number. But this is just something to be aware of because it might be that you've got a very small subtle difference wherever it was, a space at the end or maybe space at the beginning for example. And again, that wouldn't work. So this is an error that can happen with the lookup formulas where the two sets of data don't match up perfectly. So something to be aware of, just in case you see these errors is something that's worth investigating.
5. When to Use Approximate Match Lookups: So generally when using the VLOOKUP function, you'll find that you usually go the exact match option for the range_lookup part of the formula. However, there are certain occasions where the true approximate option is actually the one to use. And we're going to run through an example of whether it be the case with this data here. So we've got some race times here. We've got the minutes down here, so 20 minutes, 25 minutes, 30 minutes, et cetera, and the prices that go along with it. So a $100 if you run the race and 20 minutes, $80 if you run the race in 25 minutes, et cetera. And what we wanna do VLookup formula to return the prize value for these rays times here. But what you'll notice is is that these base times here, I will slightly different to the numbers over here. So none of them match up perfectly. We've got sick 21 minutes rather than 20 minutes, 30 minutes rather than 30, and then 44 rather than 40 or 45. And none of them work in the sense that they match up exactly. So that's where true is gonna come in. So if we do the VLookup formula, select our lookup value, which is the race time than our table array over here. And just lock that column index number two, and then the range lookup. So rather than going with folks exact match, we're gonna go with true approximate match and then close this off and we just drag this down. You'll notice that the price sells populate just fine. And we can see that for 21 minutes we've got the price of a $100. So it's pulling in this value here, 32 minutes, we've got the price of $60, so it's pulling in this value here. And then 44 minutes, we've got the price of $20 and is pulling in this value here. Now one thing you'll notice is is that 44 minutes is obviously closer to 45 minutes. So in some ways it might make sense to think that it would return this value instead of $5. But the way the formula works is that we will take the uppermost match. So in this case it's going with, It's matching it with 40 minutes and it's taking the $20 value instead. So that's fine, but it's just something to be aware of when using this formula, just so you know how it works. So that's an example of the true approximate match option. As I say. Not something you're probably going to use all that often, but definitely worth being aware of.
6. The HLOOKUP function: In this video, we're gonna look at the HLookup function. Now the HLookup function works in the same way as the VLookup function, except it's H for horizontal rather than the vertical, which means you're looking at the different rows as opposed to the different columns in your table array. So we've got arizona stores here, massa, Scottsdale, toxin, Gilbert, Phoenix, et cetera. And we've got the key one to q four cells in this format, so in different rows. Now, this is probably the less common way that you would illustrate this data, which is typically this type of data, which is typically why VLookup and more popular and more commonly used function. But sometimes you get data in this format as well, which is why HLookup is really useful. So let's say we want to bring in the queue for sales for the Phoenix store. And we can do this with a HLookup function. So as I say, it works in the same way as v lookup. So we need to specify our lookup valued. So that's gonna be Phoenix. And then next up is the table array. So we'll just select all of these cells. And then we'll lock that. Now. Next we need specify the row index number rather than the column index number. So as it is sales, we need to count the number of rows going from the top one. So we've gone very 12345, so we just specify very five there. And then folks for exact match, close it off and then enter and we get $429,035. And we can see that that's the value there. So the function has looked across this right here. It's found Phoenix and then it's gone 1234 down to row five and its return that value. So that's the HLookup function. Well, but as I say, it's something that you're probably going to use less often than you would VLookup worth being aware of that as well.
7. The INDEX function: In this video, we're gonna take a look at the index function. Now the index function is not a function is really useful on its own and it's not something you're ever really going to use on its own, but it is really powerful when combined with other functions such as match. So this video is just to illustrate what the index function isn't, how it works by itself. So if we open up the index function and what we're gonna do is return the Richmond Q2 sales figure. And we have our sales data over here. First of all, we select our array or reference. And for that we're going to select all of these columns a through to see and you see a pairs like that. And then we need to specify the row number. And Richmond is just in rho. So we specify seven and then the column number. And there'll be 123 to get q2 sales. And what you'll notice is there is also the option to specify an area number. And this is when you've got two separate references, which can be useful in certain instances, but the use cases of aero rule sort of fairly limited. So I won't run through that in this video. And then if we just close this off, you'll see that we get our figure there, $834,315, which is the same that we have enrichment, $834,315. So that's how the index function works on its own. Not very useful, but It's going to be useful when we combine them, as we'll see in the next classes.
8. The MATCH function: In this video, we're going to look at the match function. Now the match function like index is not a function that you're really going to use on its own. And it may seem a little bit pointless learning it. But when the index and match function are combined, they're incredibly powerful. So we're gonna quickly one free the match function. So what the match function does is it looks through a row, column or table and returns the position of a particular value. So let's just run through an example for that. So if we talk about match, first off, we need specify the lookup value. So for that which can select Richmond. And then the lookup array is the cells are going to look in. So we're going to just select all of these in column a. And then next up is the match type. Now for this, we're going to select the exact match, so it matches exactly with Richmond is. And then just close this off and then you see we get seven. So what this match function has done is it's counted 1234567, and it's returned that value because that's where it is in this column of data. So that's the match function. As I say, not particularly useful in zone, but you'll see how easily as when combined with index.
9. How and Why to Combine INDEX and MATCH: In this class, we're going to combine the index and match formulas to create a lookup formula. And you'll see how powerful they really are once we put them together. So we've got our sales data over here, and we're gonna use the index and match functions to look up the QT sales for Richmond. So we start off with index. Then we need to specify the array where our data is for that. These columns here. So we've got from a to C encompassing the store location and q2 cells. Then we need specify the match function. And the lookup value is going to be Richmond. So we're going to select cell G2. And then we need to specify the lookup array. And that's gonna be all of column a. We could just select from satellite to down to the bottom, but just for convenience on clicking on the whole of the column. And then we select the match type and that's going to be exact match. Close this off. And then now we need to finish off the index formula as the match function nested inside. And we need specify the column number. So this is going to be called three because it's 123 across the QT sales. And then we're gonna close this off, hit enter, and we get the value of a3 for three, 1-5. And if we look in our table, we can see Richmond. The QT says Values A3 for three, 1-5. Next up, we're going to create a formula with index and match that doesn't rely on us entering the column number. And that's because it's going to be a two-way lookup that actually references the cell H1 with QT sales in and gt Richmond in. So we'll delete this and create an even more powerful version of the index and match combo formula. So once again, index array. This time we're going to select all of these columns. And you'll see why in a minute, because we're actually going to create a formula that can capture any data value and look up any data value in this whole table going from key ones, key four, so 88. Now we need to do the first of the match formula. And we're going to specify cell GET with our store location enrichment in. And then lookup array will just be Column a because that's where all the stores are. Once again, exact match. And then we close off this and now we're gonna do a, another match function. And this time this is going to be for the quarter. So here we're going to select cell H1 with QT salesman. And we're now going to specify these top sales here, the top. And that's because this is, this lookup value's going to be identified across these cells here. And then once again, exact match. And then this time we need to enter another bracket closed after that just so it's some completes the whole formula. And if we hit Enter, we get the same value of eight free for three 1-5. However, what this is doing is it's looking at the exact value of these two, rather than just looking at Richmond and then going 123 and the column numbers. And what this means is that if we would say change this to Q3 sales, this automatically updates as well. So we now get 740866 and we can see that's the value there for Richmond. Likewise, we could also change the store location Valley. So we could change this to Portland, for example. And this again updates automatically. You can see Portland down hair Q3 sales 711439711439. So that should indicate just how useful the index and match functions are when they're combined together. And the powerful lookup formulas that you can create with them.
10. The XLOOKUP Function: So in this video, we're going to run through a very basic example of the HLOOKUP function. Now x lookup is a relatively new function in Excel, and it's only available to Office 365 users. So whilst it is more powerful than the lookup and also more powerful than index match. It's worth being aware of VLookup, an index match, and HLookup as well as x lookup. Although you may find that you much prefer using x lookup and you kind of disregard some of the other ones. With x look up. If you have x lookup formulas in a workbook and you send that to someone who's using a version of Excel that doesn't support X lookup formulas. It will not work for them, so just be aware of that. But anyway, let's dive into next lookup example. So we've got some basic data here. Runners back did a grace in June and they have various positions and they had that time here as well. Now, let us use an extra cup function to return the time in minutes for married. So like VLookup, first of all, you specify the lookup value. So we'll select marry them. Now, next up, you specify the lookup array. So rather than doing a whole table array, you do the lookup array and the returned array separately. And that has benefits which will come on to. So the lookup array is going to be this column here where we have the runner names. And we'll just lock that. And then the return array as we're looking for the time in minutes, is going to be the cells here. So you'll notice that we don't have to highlight all of these and then specify certain column. Now, the remaining arguments of the formula you don't actually need to enter. So we will just close this off now and actually works. So we've got Mary there, the runner, and if we look across to her tiny minutes, it's 44 minutes and that's working just fine. So that's a very simple example of the HLOOKUP function, and we'll come on to some more areas of it in the following videos.
11. Using XLOOKUP's 'If Not Found' Feature: In this video, we're gonna take a quick look at the if NOT_FOUND option in the VLOOKUP function. So we've got our data here with the runners in column a, race stated in column B, and then the position and time. Now we go through each one and say, and what we want to do is just return the time that they ran into these cells here. So we'll start off with our x lookup function. Specify our lookup value. Now the lookup array, which is the one is names, block those, and then the return array, which is the time in minutes. And then we're gonna specify if not found. So what you'll notice, peter doesn't actually feature in this list. And if we don't specify anything here, and we can do an example of that. What will happen is we get an NA there which doesn't look very good and it just doesn't tell us kind of what's going on. But if we return to the formula, we can actually specify what we want to appear in the cell if one is not found. So we can put didn't raise something similar events and then we get didn't race. So we've got the time Malala, The Time Brad, and we know the reason there's no Valley because petered in race in the gene risks.
12. Using XLOOKUP's 'Exact' Match Modes: In this video, we're going to look at three different types of match mode that you can find in the HLOOKUP function. So here we've got different race times and the price that goes along with it. So 20 minutes gets you a $100.20-five minutes gets you $80, et cetera. And we've got some race time results here from a race that was done in September. And what we want to do is pull in the price value for each of these three times for Zoe, Layla, and Brad. So we write our x lookup formula. And the lookup value will be the time. And then the lookup array is going to be the time in these cells here. Lock those, and then the returned array will obviously be the prizes. If not found. We're just going to skip past this. And then we're going to look at these three different types of exact match options here. So exact match, exact match, or next moral rights, exact match or next larger item. So first of all, let's go with this first one, exact match. Now, what you'll notice is here, none of these work apart from the 30 minutes one because the 30-minute One is the only one that has an exact match over here, $60 and proposal in 21 minutes and 44 minutes. Neither of these two times are found in these cells here. So we're going to need a different match type. So let's just return to the formula and change this. This time we're gonna go with exact match or next smaller item and then close that off and then we'll pull this down. Now you can see this works and we do have a prize for each of these times. And it's taking the exact one in the case of 30.21, $60, it's taking the smaller of these two. So a $100 and smaller as the 20 minutes, hey, rather than the 25 minutes. And then for 44 minutes, again, it's taking the smaller of these two, so 40 as opposed to 45 and it's returning $20. That if we wanted to do this slightly differently, we could change this part of the formula. It takes the exact match or next larger item. And what that will do is this. You can see that's gone down to $80. This is state at 60, and this one's gone down to $5. And that's because in the case of 21, rather than taking this a $100 valley here with taking this one, because it's based on the next largest item after 20, so 25. And then here it's taking $5 rather than the $20 value because we are looking at the 45 now rather than 40. So those are three of the match modes with nslookup who really useful to use in different situations. So it's worth being aware of them.
13. Using XLOOKUP's 'Wildcard' Match Mode: In this video, we're going to look at the wildcard match mode in the lookup function. Now we've got our runners overhead. However, in this video, the runners have their surname as well. So Zookeeper, later thompson, Brian Clark, et cetera. However, with our lookup values, in this example, we don't have the surnames, and I just wanna show you how you can still get the time without my information. So if we open up our x lookup function, we can then select runner. So I will select this cell here. And then what we need to add is an ampersand sign. Open up this quotation mark and an Asterix, and this represents anything. So this means it can be Layla and an anything afterwards and it will still match the valleys or the hair where the surname is in place. For the lookup array, we're going to select the runner names locked planets, and then return array will be the time for if not found, we will just skip past that and then match mode. This is where we need to select two wildcard character match. And then just close this off. And then we pull this down. You'll see that it works for both of them because the way it's working is it seeing Layla and then it's effectively allowing anything afterwards to be there so we can still return a value even though the values of a have the surname. Now, hypothetically speaking, you can also have the asterix before the exact lookup value here in case there was something beforehand. So let's just run through a quick example of that. So let's say we add in a space next to these two runners. You'll see that we get the NA sign appear. And that's because we haven't allowed for anything to come before this value. So what we can do is just add in another asterix, the ampersand again, it enter and then drag this down and you'll see that it works. So that just shows how flexible the x lookup function can pay. And obviously, we did change this match mode to exact match. It wouldn't work because it is an inexact match because we have to allow for the astrophysics before and after the work in this function. So that's just not a great way in which the x lookup can be used to even more flexible when you're doing lookups.
14. Changing XLOOKUP's Search Mode: In this video, we're going to look at the search mode feature in the lookup function. So our data over here has two bases, a June race and a July race. And the runners that took part in both races are exactly the same. So Zoe, Layla, Brad, John, Mary Tom, and we see the same names down here as well. Now, they came in different positions and they also finished with different times. So if we would do simple x lookup function to look for the time of Zoe. We will do that now. And what you'll notice is, is that the first value of these is returned. So let's just do that. Both this off, so we get 20 minutes. And you can see that the x lookup function has found this first value, looked across and found that 20. Now you can however, search data from the ends or the bottom up to the top with the x lookup function. And do this, we can go through to the search mode path formula. So if we skip past, if not found, skip past match mode, and then get through to search mode. We can swap search first-last, which is the default one, which we don't even need to specify to search last first and then select that we get minus1, hit enter, and we get 26 minutes. So now what the formula is doing is it's looking from the bottom of the table, it's going up, it's finding this instance of Zoe, and then it's returning that 26 minute value. So just another example of a useful feature within the VLOOKUP function.
15. How to Use XLOOKUP for Horizontal Lookups: In this video, we're going to look at the x lookup formula and how it can be used to return a value when the lookup array and return array are horizontal. And we are going to use the exact same data that we used in the HLookup tutorial, just to illustrate how these two formulas can achieve the same thing. So we've got our sales data for Arizona stores here going from key ones Q4. And we're going to return the key for sales figure for Phoenix. So we open up our x lookup formula, select the lookup value of Phoenix, and then the lookup array will be the cells along here, horizontal. And there's a lot that for best practice. And then the returned array will be our Q4 sales figures down here like that. And then we can just leave the rest of the fields blank. So close this off and you can see that we get the 49035 finger there. And that's the exact same figure here. Phoenix key for sales, $429,035. So that's just an example of using the VLOOKUP function in the same way that you'd use a HLookup function.
16. Using XLOOKUP for a Lookup Array to the Right: So one of the limitations of the VLookup function is that your return data has to be to the right of the lookup values. Now with the VLOOKUP function on the other hand, it doesn't matter if you're lookup values are to the left or to the right, you can specifically select the cells where your return data is. So in this data here, I've rearranged it so the store location is over to the right. And we've got all of our QE1, QE2, QE3 key for sales data's able to left. Now, here we are going to enter in an extra cup function. And obviously the lookup value would be the stores here. Now the lookup array, we're going to specify these cells here. And then for the return array, we can just go over here. And because we're interested in key one sales, we can just select the cells and it doesn't matter that they're either to the left because we're not basing the formula or ground or certain table array and column numbers. And then we can just close this off. It ends. And there we go and just drag this down. And you can see the numbers popular just fine. So for example, or gusto 62932, we look down here, we can see August 6123932. So it's working just fine. And that's another strength of the extra cup function. The VLookup function.
17. Next Steps: Thanks for taking the Lookup and Reference functions class and I hope you enjoyed it. Let me know if you have any questions and you can find the class project instructions below to test what you've learned. If you want to learn more by Excel, take look at my other classes and follow me for updates on my new classes. Once again, thanks for taking the class and I hope to see you in the next one.