1 00:00:00,270 --> 00:00:03,330 Now that we understand the purpose of a lookup function, let's look at 2 00:00:03,330 --> 00:00:07,680 how to actually set one up using the Excel spreadsheet we just looked at. 3 00:00:07,680 --> 00:00:12,500 Before we create the VLOOKUP, we need to make sure our data is formatted properly. 4 00:00:12,500 --> 00:00:15,210 What I mean by that is that if we're going to do a VLOOKUP, 5 00:00:15,210 --> 00:00:19,500 we need to make sure our data is organized in columns since a VLOOKUP 6 00:00:19,500 --> 00:00:21,420 does a vertical search in a column. 7 00:00:22,540 --> 00:00:25,230 So you can see here on our spreadsheet that our data 8 00:00:25,230 --> 00:00:28,290 is nicely formatted in columns with the row at the top for 9 00:00:28,290 --> 00:00:33,600 headers, county, state, and then the number of bedrooms across. 10 00:00:33,600 --> 00:00:37,230 Now we want to pull over the number of housing units by county 11 00:00:37,230 --> 00:00:38,360 to our rental price tab. 12 00:00:39,400 --> 00:00:42,482 First, let's label column H as Housing Units. 13 00:00:46,583 --> 00:00:50,050 After we do that, we can select the cell below the header. 14 00:00:50,050 --> 00:00:53,430 The first cell we want to see the new data in and 15 00:00:53,430 --> 00:00:57,980 then go up to the Formulas tab, Lookup and Reference. 16 00:00:57,980 --> 00:01:03,095 And then at the very bottom, you'll see VLOOKUP, select that. 17 00:01:03,095 --> 00:01:06,960 Now you will notice a box pops up with four blanks. 18 00:01:06,960 --> 00:01:10,150 These are the four input areas for the VLOOKUP formula. 19 00:01:10,150 --> 00:01:14,660 The first is the Lookup_value, which is the value you are looking for. 20 00:01:14,660 --> 00:01:18,960 In this case, it would be the county which is what we're using to match the data from 21 00:01:18,960 --> 00:01:21,030 one tab to the next. 22 00:01:21,030 --> 00:01:25,645 So for row two, we want to return the number of housing units for 23 00:01:25,645 --> 00:01:28,967 Anderson County so we would select cell A2. 24 00:01:31,265 --> 00:01:35,363 The second is the Table_array, where the range you want to search in, 25 00:01:35,363 --> 00:01:38,525 which in our example is on the Population & Units tab. 26 00:01:41,025 --> 00:01:46,010 When selecting the range, the Lookup_value must be in the farthest left column. 27 00:01:46,010 --> 00:01:50,140 So county or column A will be our farthest left column. 28 00:01:50,140 --> 00:01:54,350 The data you want to retrieve can then appear in any column to the right. 29 00:01:54,350 --> 00:01:57,196 We want to return housing units so 30 00:01:57,196 --> 00:02:01,373 the range we would select is columns A through C. 31 00:02:01,373 --> 00:02:05,577 One thing I want to mention here, in the example, the range I selected included 32 00:02:05,577 --> 00:02:10,320 entire columns, meaning all the way down to the bottom of the spreadsheet. 33 00:02:10,320 --> 00:02:12,470 Now if we decide not to choose entire columns for 34 00:02:12,470 --> 00:02:16,060 the range, we need to make the range an absolute reference. 35 00:02:16,060 --> 00:02:20,937 On a PC, you can do this by placing your cursor in the range selection in 36 00:02:20,937 --> 00:02:24,672 the formula bar or VLOOKUP box in hitting the F4 key. 37 00:02:28,412 --> 00:02:31,803 For more on absolute references including how to do this on a Mac, 38 00:02:31,803 --> 00:02:34,280 please see the teachers' notes. 39 00:02:34,280 --> 00:02:37,130 The absolute reference will make sure the range is static. 40 00:02:37,130 --> 00:02:41,610 So when you drag the formula down, it won't change the placement of your range. 41 00:02:41,610 --> 00:02:45,450 I usually just play it safe and select the entire columns for the range. 42 00:02:45,450 --> 00:02:48,362 The next parameter is the Col_index_num, 43 00:02:48,362 --> 00:02:53,880 which is the number of the column that you want to return from your range. 44 00:02:53,880 --> 00:02:57,242 So in our example, we are wanting to return Housing Units, 45 00:02:57,242 --> 00:02:59,745 which is the third column over in our range. 46 00:03:02,545 --> 00:03:04,915 So we'll type in the number 3. 47 00:03:07,090 --> 00:03:10,948 One important thing to remember here is that VLOOKUPs only look right, 48 00:03:10,948 --> 00:03:14,164 meaning you can't put in a negative column index number for 49 00:03:14,164 --> 00:03:15,980 it to search columns to the left. 50 00:03:15,980 --> 00:03:18,820 The last input is the Range_lookup. 51 00:03:18,820 --> 00:03:20,180 This one is optional. 52 00:03:20,180 --> 00:03:25,130 You can either put false, which returns an exact match, or if you put true or 53 00:03:25,130 --> 00:03:27,770 leave blank, it will return an approximate match. 54 00:03:29,890 --> 00:03:33,710 It's best to use false when working with unique identifiers. 55 00:03:33,710 --> 00:03:36,480 In this case, make sure the county names match. 56 00:03:36,480 --> 00:03:41,160 And you'll want to use true in cases where you're not matching on a unique ID. 57 00:03:41,160 --> 00:03:42,720 But you're looking for the best match or 58 00:03:42,720 --> 00:03:47,360 best category in cases where there are no exact matches. 59 00:03:47,360 --> 00:03:49,720 If you decide to use approximate match, 60 00:03:49,720 --> 00:03:53,670 your data must be sorted in ascending order, where you make incorrect results. 61 00:03:55,510 --> 00:03:59,890 Once we've populated the four boxes, hit OK, and you'll see the result. 62 00:03:59,890 --> 00:04:04,920 Now we can see that Anderson County has 20,116 housing units. 63 00:04:04,920 --> 00:04:06,760 Let's check to make sure that is correct. 64 00:04:11,049 --> 00:04:14,910 There's Anderson County and 20,116, so it worked. 65 00:04:15,960 --> 00:04:20,184 So to recap, the VLOOKUP search for Anderson County in the range we specified, 66 00:04:20,184 --> 00:04:24,471 and it returned whatever was in the third column on the same row is Anderson County 67 00:04:24,471 --> 00:04:26,191 on our Population & Units tab. 68 00:04:29,251 --> 00:04:32,356 Now we can simply drag this formula down, we'll double click and 69 00:04:32,356 --> 00:04:36,190 you'll see the formula copies all the way down to the bottom. 70 00:04:36,190 --> 00:04:40,000 Now let's say we also want to see what the population of each county is. 71 00:04:40,000 --> 00:04:44,230 We can also pull this over from the other tab by using a VLOOKUP. 72 00:04:44,230 --> 00:04:47,456 But this time instead of choosing VLOOKUP from the formula tab, 73 00:04:47,456 --> 00:04:52,050 let's try typing in the formula, which is the way I usually do it. 74 00:04:52,050 --> 00:04:55,321 The first thing we wanna do is label column I as Population. 75 00:05:00,901 --> 00:05:06,630 And then we can place our cursor in cell I2 and type =vlookup( and 76 00:05:06,630 --> 00:05:13,400 then you'll see a pop up that helps you remember the four parts of the formula. 77 00:05:14,630 --> 00:05:18,195 Remember for the lookup value, we want to select county, 78 00:05:18,195 --> 00:05:22,502 so you can do that by selecting it with your mouse or you can type in A2. 79 00:05:22,502 --> 00:05:26,127 Then put a comma, you'll put commas in between each command and 80 00:05:26,127 --> 00:05:29,970 now choose your table array which is the range you want to search in. 81 00:05:31,130 --> 00:05:33,341 So go over to Population & Units tab and 82 00:05:33,341 --> 00:05:37,503 select all four columns this time since we are wanting to get population. 83 00:05:40,101 --> 00:05:44,546 Then another comma, and this time we will type in 4 as the column index since 84 00:05:44,546 --> 00:05:47,870 the population is the fourth column in a range. 85 00:05:47,870 --> 00:05:52,740 And then put another comma and type in or choose false and 86 00:05:52,740 --> 00:05:56,000 then close parenthesis and hit Enter. 87 00:05:56,000 --> 00:06:00,398 Now, you will see the population for Anderson County is 58.458. 88 00:06:00,398 --> 00:06:04,980 Now, you can copy this down by double-clicking. 89 00:06:04,980 --> 00:06:07,900 Now that we've walked through a VLOOKUP, why don't you try and 90 00:06:07,900 --> 00:06:14,005 create an HLOOKUP by adding the population to the tab labeled HLOOKUP- Median Rental 91 00:06:14,005 --> 00:06:19,030 $ by pulling from the tab labeled HLOOKUP- Population & Units. 92 00:06:19,030 --> 00:06:21,920 Remember that HLOOKUP is just like a VLOOKUP but 93 00:06:21,920 --> 00:06:25,640 instead searches horizontal rows instead of vertical columns. 94 00:06:25,640 --> 00:06:26,820 After you've tried on your own, 95 00:06:26,820 --> 00:06:29,620 you can meet me in the next video and I'll show you how I did it.