1 00:00:00,940 --> 00:00:05,640 The "VLOOKUP" function is one of the most used look up functions in Excel. 2 00:00:05,650 --> 00:00:11,080 It allows you to look up values from another place, another sheet, or another data table. 3 00:00:11,080 --> 00:00:14,830 The great thing about it is it works in all versions of Excel. 4 00:00:15,070 --> 00:00:16,720 Let's take a look at our example. 5 00:00:16,840 --> 00:00:20,740 We have a list of names, the yearly salaries. 6 00:00:20,750 --> 00:00:25,840 Now we need to get the department and the bonus percentage for each person. 7 00:00:25,840 --> 00:00:28,430 That information was sent to us. 8 00:00:28,480 --> 00:00:32,710 Let's assume by our HR department and it's in the tab master data. 9 00:00:32,710 --> 00:00:36,260 So here we have a lot more names, the division, 10 00:00:36,280 --> 00:00:44,410 each person works in, the department. And on this side we have a list of salaries and the bonus percentage 11 00:00:44,830 --> 00:00:47,920 associated with each level of salary. 12 00:00:47,920 --> 00:00:52,780 So based on this information we need to figure this out. 13 00:00:52,840 --> 00:00:57,210 Now of course on smaller data sets you can do all of this manually right. 14 00:00:57,220 --> 00:01:01,990 You can take a look at Gary Miller, go here, look for Gary Miller, 15 00:01:01,990 --> 00:01:05,410 Copy and paste his department or division there, 16 00:01:05,440 --> 00:01:10,290 Take a look at which level salary he falls into, copy and paste this value in. 17 00:01:10,540 --> 00:01:15,010 But the moment you have a lot of data, your master data is huge 18 00:01:15,010 --> 00:01:21,140 you need to do this in a dynamic way. That's when you can use the "VLOOKUP" function. 19 00:01:21,260 --> 00:01:25,160 Let's do an example with department and I'm going to explain the function here. 20 00:01:25,210 --> 00:01:28,120 So we're going to start off with "VLOOKUP". 21 00:01:28,120 --> 00:01:29,940 It needs a few arguments. 22 00:01:29,950 --> 00:01:32,100 First off is the lookup value. 23 00:01:32,110 --> 00:01:35,390 Which value are we looking up? 24 00:01:35,920 --> 00:01:38,140 Well, we're looking up this name. 25 00:01:38,290 --> 00:01:42,190 Then the next argument is where are we looking this up? 26 00:01:42,190 --> 00:01:45,340 Well, we're looking it up in this table. 27 00:01:45,340 --> 00:01:53,980 But notice something special about this dataset and what I'm highlighting. The name of the person that 28 00:01:53,980 --> 00:02:01,330 I'm looking up is in the left hand side. And division and department are on the right hand side. 29 00:02:01,450 --> 00:02:08,979 I can't highlight this because it's never gonna find Gary Miller in here. It's going to look in the first 30 00:02:09,370 --> 00:02:13,260 column on the left hand side for my lookup value. 31 00:02:13,540 --> 00:02:15,090 That's not the right column. 32 00:02:15,160 --> 00:02:16,330 I need this. 33 00:02:16,930 --> 00:02:20,580 I want department back and division is in the way. 34 00:02:20,710 --> 00:02:21,610 It doesn't matter. 35 00:02:21,610 --> 00:02:25,040 You have to highlight one range of cells. 36 00:02:25,090 --> 00:02:31,930 Even if I have five or six different columns between the name and the department, I have to highlight 37 00:02:31,930 --> 00:02:33,180 them as well. 38 00:02:33,300 --> 00:02:42,050 So remember, important part is that the lookup value is on the left hand side of my table array. 39 00:02:42,130 --> 00:02:44,580 Next is the column index number. 40 00:02:44,620 --> 00:02:47,350 That's the column I want returned. 41 00:02:47,530 --> 00:02:52,450 In this example it's department and I have to type a three in here. 42 00:02:52,450 --> 00:03:00,180 The reason is it's the third column in the range that I highlighted. Last argument is very important. 43 00:03:00,200 --> 00:03:04,790 That's if you want an approximate match or an exact match. 44 00:03:04,960 --> 00:03:11,050 It's an optional argument so people tend to forget about this but it's actually important. 45 00:03:11,050 --> 00:03:14,020 Otherwise you might get the wrong answers. 46 00:03:14,020 --> 00:03:16,200 In this case I want an exact match. 47 00:03:16,210 --> 00:03:21,020 I wanted to look for exactly Gary Miller and give me back the department. 48 00:03:21,040 --> 00:03:22,900 So I have to go with false. 49 00:03:22,930 --> 00:03:24,660 We can also put a zero 50 00:03:24,700 --> 00:03:26,630 instead of putting in false. 51 00:03:26,700 --> 00:03:29,550 Now when I press enter, I get finance. 52 00:03:29,550 --> 00:03:32,380 Gary Miller works in the finance department. 53 00:03:32,380 --> 00:03:33,820 Let's just double check. 54 00:03:33,820 --> 00:03:38,080 He does. Now before I copy this formula down. 55 00:03:38,110 --> 00:03:42,850 There is some cell referencing that I need to take care of. This one A5, 56 00:03:42,850 --> 00:03:49,440 This I need to be dynamic so I don't need to fix the column or the row here. For this data set 57 00:03:49,450 --> 00:03:54,180 that's something I don't want to shift at all. Press F4 and now press enter. 58 00:03:54,550 --> 00:03:56,470 Let's just send this down. 59 00:03:56,470 --> 00:03:58,770 We get our departments. For the last one, 60 00:03:58,850 --> 00:04:00,190 I have an error. 61 00:04:00,190 --> 00:04:02,300 And that's the #N/A error. 62 00:04:02,380 --> 00:04:09,730 A very common error when you work with "VLOOKUP". The general cause of this is the value you're looking 63 00:04:09,730 --> 00:04:12,210 up doesn't exist in your dataset. 64 00:04:12,220 --> 00:04:16,720 So a way around this is to use the "IFERROR" function. 65 00:04:16,839 --> 00:04:20,529 But there is another error function that came since Excel 2016. 66 00:04:20,529 --> 00:04:29,430 If you have Excel 2016 and above, you can use "IFNA". The value is actually our VLOOKUP. 67 00:04:30,020 --> 00:04:37,640 And if it's an error, we want nothing returned. Close bracket and push this down. 68 00:04:37,690 --> 00:04:46,300 If you have a version of Excel that's before Excel 2016 use the IFERROR function instead. 69 00:04:46,480 --> 00:04:46,750 Okay. 70 00:04:46,750 --> 00:04:51,120 Now what happens if you leave out that last argument. 71 00:04:51,160 --> 00:04:54,800 Well if you do that there's one thing you need to make sure of. 72 00:04:54,880 --> 00:05:00,720 If the column that has your look of value is sorted you can leave out the last argument for the VLOOKUP. 73 00:05:00,720 --> 00:05:06,660 But if you're not sure whether it's sorted or not or if the sorting could change then make sure you 74 00:05:06,660 --> 00:05:10,410 put in the false for that last argument. 75 00:05:10,410 --> 00:05:14,580 Now let's take a look at doing the VLOOKUP for the salary. 76 00:05:14,670 --> 00:05:21,750 So here I have exact salaries of each person but I want to take a look at this table that shows me the 77 00:05:21,750 --> 00:05:28,430 bonus scheme and I want to allocate the bonus percentage for each person based on the scheme here. 78 00:05:28,440 --> 00:05:31,430 So now I can't use an exact match. 79 00:05:31,500 --> 00:05:38,280 I have to use the approximate match. But there is one thing I need to make sure of when ever I use approximate 80 00:05:38,280 --> 00:05:47,250 matches in the VLOOKUP function. And that's my look up column has to be sorted and this is sorted. 81 00:05:47,250 --> 00:05:54,780 So now I can comfortably go back and use the VLOOKUP function. First argument is the look up value. 82 00:05:54,900 --> 00:05:56,400 What's that going to be in this case? 83 00:05:57,120 --> 00:05:58,910 It's my actual salary. 84 00:05:58,980 --> 00:06:05,150 The table array is sitting in master data and it's these two, right? 85 00:06:05,210 --> 00:06:13,230 Make sure the look up value is the first column that's included in the table array. And it is. The column 86 00:06:13,230 --> 00:06:14,520 index number. 87 00:06:14,520 --> 00:06:16,580 What do I want returned? 88 00:06:16,740 --> 00:06:18,180 I want the bonus returned. 89 00:06:18,180 --> 00:06:23,220 What's the index number for this? It's the second column in the range that I selected. 90 00:06:23,250 --> 00:06:29,740 I'm gonna put a two and for the last argument notice that it's in square brackets. 91 00:06:29,820 --> 00:06:33,070 This means it's an optional argument in Excel. 92 00:06:33,120 --> 00:06:37,060 True is the default option which is an approximate match. 93 00:06:37,140 --> 00:06:43,500 So I'm going to leave it out, close bracket press enter. And before I push this down, what do I need to make sure? 94 00:06:43,500 --> 00:06:48,440 That I've done the fixing for the cell reference correctly. For this one, 95 00:06:48,630 --> 00:06:49,600 I need to fix. 96 00:06:49,650 --> 00:06:54,660 So let's push this down and I get the bonus percentage for each person. 97 00:06:54,690 --> 00:06:56,170 Let's just double check. 98 00:06:56,220 --> 00:07:03,540 Robert Marquez earns one hundred thirty four thousand, so he must have the highest bonus percentage. 99 00:07:03,900 --> 00:07:07,760 Which is correct, right? It's above 100,000. 100 00:07:07,770 --> 00:07:11,100 Let's take a look at someone who's earning a 10 percent scheme. 101 00:07:11,100 --> 00:07:15,600 That means they must be earning between 60,000 to a 100,000. 102 00:07:15,720 --> 00:07:16,830 So let's go here. 103 00:07:16,830 --> 00:07:18,380 That's Robert Spear. 104 00:07:18,480 --> 00:07:21,570 Gary Miller and Kathrin Nehmeh. 105 00:07:21,810 --> 00:07:28,020 So for Kathrin Nehmeh, we couldn't find her department but we could get the bonus because the driver 106 00:07:28,020 --> 00:07:30,600 of that lookup table is the yearly salary 107 00:07:30,600 --> 00:07:32,950 and we have that information. 108 00:07:33,050 --> 00:07:33,350 Okay. 109 00:07:33,370 --> 00:07:36,810 So that's how you can use the VLOOKUP function. 110 00:07:36,870 --> 00:07:42,590 Just remember that it's important to understand how to use the last argument for it. 111 00:07:42,640 --> 00:07:44,910 Anytime you're looking for an exact match 112 00:07:45,090 --> 00:07:48,570 don't forget to put false for that argument.