Using VLOOKUP to Search in a Column

Now that we understand the purpose of a lookup function, let's look at 0:00 how to actually set one up using the Excel spreadsheet we just looked at. 0:03 Before we create the VLOOKUP, we need to make sure our data is formatted properly. 0:07 What I mean by that is that if we're going to do a VLOOKUP, 0:12 we need to make sure our data is organized in columns since a VLOOKUP 0:15 does a vertical search in a column. 0:19 So you can see here on our spreadsheet that our data 0:22 is nicely formatted in columns with the row at the top for 0:25 headers, county, state, and then the number of bedrooms across. 0:28 Now we want to pull over the number of housing units by county 0:33 to our rental price tab. 0:37 First, let's label column H as Housing Units. 0:39 After we do that, we can select the cell below the header. 0:46 The first cell we want to see the new data in and 0:50 then go up to the Formulas tab, Lookup and Reference. 0:53 And then at the very bottom, you'll see VLOOKUP, select that. 0:57 Now you will notice a box pops up with four blanks. 1:03 These are the four input areas for the VLOOKUP formula. 1:06 The first is the Lookup_value, which is the value you are looking for. 1:10 In this case, it would be the county which is what we're using to match the data from 1:14 one tab to the next. 1:18 So for row two, we want to return the number of housing units for 1:21 Anderson County so we would select cell A2. 1:25 The second is the Table_array, where the range you want to search in, 1:31 which in our example is on the Population & Units tab. 1:35 When selecting the range, the Lookup_value must be in the farthest left column. 1:41 So county or column A will be our farthest left column. 1:46 The data you want to retrieve can then appear in any column to the right. 1:50 We want to return housing units so 1:54 the range we would select is columns A through C. 1:57 One thing I want to mention here, in the example, the range I selected included 2:01 entire columns, meaning all the way down to the bottom of the spreadsheet. 2:05 Now if we decide not to choose entire columns for 2:10 the range, we need to make the range an absolute reference. 2:12 On a PC, you can do this by placing your cursor in the range selection in 2:16 the formula bar or VLOOKUP box in hitting the F4 key. 2:20 For more on absolute references including how to do this on a Mac, 2:28 please see the teachers' notes. 2:31 The absolute reference will make sure the range is static. 2:34 So when you drag the formula down, it won't change the placement of your range. 2:37 I usually just play it safe and select the entire columns for the range. 2:41 The next parameter is the Col_index_num, 2:45 which is the number of the column that you want to return from your range. 2:48 So in our example, we are wanting to return Housing Units, 2:53 which is the third column over in our range. 2:57 So we'll type in the number 3. 3:02 One important thing to remember here is that VLOOKUPs only look right, 3:07 meaning you can't put in a negative column index number for 3:10 it to search columns to the left. 3:14 The last input is the Range_lookup. 3:15 This one is optional. 3:18 You can either put false, which returns an exact match, or if you put true or 3:20 leave blank, it will return an approximate match. 3:25 It's best to use false when working with unique identifiers. 3:29 In this case, make sure the county names match. 3:33 And you'll want to use true in cases where you're not matching on a unique ID. 3:36 But you're looking for the best match or 3:41 best category in cases where there are no exact matches. 3:42 If you decide to use approximate match, 3:47 your data must be sorted in ascending order, where you make incorrect results. 3:49 Once we've populated the four boxes, hit OK, and you'll see the result. 3:55 Now we can see that Anderson County has 20,116 housing units. 3:59 Let's check to make sure that is correct. 4:04 There's Anderson County and 20,116, so it worked. 4:11 So to recap, the VLOOKUP search for Anderson County in the range we specified, 4:15 and it returned whatever was in the third column on the same row is Anderson County 4:20 on our Population & Units tab. 4:24 Now we can simply drag this formula down, we'll double click and 4:29 you'll see the formula copies all the way down to the bottom. 4:32 Now let's say we also want to see what the population of each county is. 4:36 We can also pull this over from the other tab by using a VLOOKUP. 4:40 But this time instead of choosing VLOOKUP from the formula tab, 4:44 let's try typing in the formula, which is the way I usually do it. 4:47 The first thing we wanna do is label column I as Population. 4:52 And then we can place our cursor in cell I2 and type =vlookup( and 5:00 then you'll see a pop up that helps you remember the four parts of the formula. 5:06 Remember for the lookup value, we want to select county, 5:14 so you can do that by selecting it with your mouse or you can type in A2. 5:18 Then put a comma, you'll put commas in between each command and 5:22 now choose your table array which is the range you want to search in. 5:26 So go over to Population & Units tab and 5:31 select all four columns this time since we are wanting to get population. 5:33 Then another comma, and this time we will type in 4 as the column index since 5:40 the population is the fourth column in a range. 5:44 And then put another comma and type in or choose false and 5:47 then close parenthesis and hit Enter. 5:52 Now, you will see the population for Anderson County is 58.458. 5:56 Now, you can copy this down by double-clicking. 6:00 Now that we've walked through a VLOOKUP, why don't you try and 6:04 create an HLOOKUP by adding the population to the tab labeled HLOOKUP- Median Rental 6:07 $ by pulling from the tab labeled HLOOKUP- Population & Units. 6:14 Remember that HLOOKUP is just like a VLOOKUP but 6:19 instead searches horizontal rows instead of vertical columns. 6:21 After you've tried on your own, 6:25 you can meet me in the next video and I'll show you how I did it. 6:26