This Screencast is going to help you quite a bit for doing the distance calculator project. I've got a file here called Multiple Table Lookups, and this is available on the course website. What we've got, we've got multiple tables here. We have a cooking groups, maybe we have different employees that are in different groups, so cooking dishes and sweeping. In each of those groups, we have names. You'll notice that we've got multiple names so the same name is Max, for example, as shown in cooking and dishes. Heather is also in cooking and sweeping. We've got employee numbers, and maybe what we want to do is we want to create this dashboard here where we can select from a Drop-down list the three different groups, so cooking for example. Then the name, we can select, the employee, so maybe Sally, and we're going to look up and display the employee number. This is very similar to the data for states, which are going to be in capital letters and cities which are in non all-caps that you're going to have for the Distance Calculator Project, and so that's going to help you quite a bit. Again, what we're trying to do is we're trying to look up the employee number of a name of a person, but some of these groups have the same name. For example, if I want to look up Max, Max depends upon which group, is it the Max and the cooking group, or is it the Max in the dishes group? That's why we need these conditional Drop-down menus. For the distance calculator project, you've got a bunch of states, and there are some states that have the same city. For example, Boulder, Boulder is in Colorado, there's a Boulder in Utah, and I think there's a Boulder in Nevada or somewhere else in the west. This is very similar to what you're going to encounter in the Distance Calculator Project. We're going to go through this. This is actually the finish, the final product. You'll notice here that depending upon the first Drop-down menu, so the group, I'm plucking out over here, the names and their corresponding employee numbers. Cooking, I've got this array, if I switch this to dishes, I'm plucking out the names with their employee numbers in the dishes group, and the same thing for the sweeping group. Then based upon that, so let's go ahead and select Marvin, I'm just using a simple VLookup in that secondary table that I have over here. If you want, I'll show you how you can just embed this directly into that formula there. You can embed this in, so you can solve this without having anything else over here, and you can do something similar with your Distance Calculator Project. Let's go ahead and get started here I have the starter file. The first thing that we're going to do, we have to create groups, so we have to create named groups. I'm going to go ahead and select this. I go up to the Formulas tab, Create From Selection. I'm going to create that cooking group with the name in the top row. I'll go ahead and click Okay. You'll notice then if I select these names, that's named Cooking, I can do the same thing with the dishes group, Create From Selection, top row, sweeping, Create From Selection, top row, you're going to have 50 states plus the district of Columbia. You're going to have to do this 51 different times. But once you've got it, it's set in a certain stone. There's our groups. Now what we're going to do, we need to do Data Validation. A couple of ways to do this, you can get creative using some filter functions for the 50 states or you can just manually put them in. But we go up here to the Data tab, Data Validation over here, and we're going to create a list. This is just our cooking, dishes, and sweeping named groups. I can go ahead and click Okay. Now what I can do is I can select those from that Drop-down list, Data Validation. The next step is to determine which row of Column A has Cooking in it. I'm just going to go over here, maybe put a row number, in this, I'm just going to simply use the match function. I'm going to match our group over there that we selected in that Drop-down list. I'm going to look through entire Column A, so I'm looking for Cooking. Now we're assuming that Cooking, Dishes, and Sweeping only show up once, and that's a good assumption, it's a good assumption for your distance calculator project because each state has a unique name, it's very important if you're looking for an exact match to put a zero at the end. If you don't put a zero at the end, it'll still work, but it's going to give you the wrong information. Typically with the match function, you want to put a exact match or a zero at the end. This is telling us that Cooking is found in Column A in the third row. I can do Dishes, and that's in the 10th row, Sweeping is found in the 15th row. Now we're going to use this in order to pluck out the sub tables for each of these groups. Before we do that, let's go ahead and put in the Conditional Data Validation here. This is similar to some of the other Screencasts that I've shown in Part 2 of the course. We can go up here, click on Data Validation, we want this to be a list. Now there's a nice way to do this because we can simply use the indirect function, the indirect function of the group name. For your project you're probably also going to want to use the substitute function because there are names of states that have spaces in them and named ranges cannot have spaces in them. I've shown you how to do that in some previous Screencasts. I can go ahead and click Okay, and that means the second Drop-down list only has the names of those individuals that are in the Sweeping group, and so I can select Stephanie, for example. Now what we want to do is we want to pluck out for the sweeping group, we want to pluck out this array. I'm just going to do that over here. To do that, I'm going to use the offset function. We're going to offset the first argument of the offset function. You might want to go back and review this function from the previous courses. We start with a reference and I'm just going to start with A1. What we're going to do is we're going to offset by the number of rows. For the sweeping array, I'm actually going to offset simply by the row number. If I offset from Row 1,15 rows, I'll get down to Row 16 and that'll be the first item or row of our sub array here. I'm going to offset zero columns because I don't want to offset columns at all, and then the rows, the Sweeping array down here, the array that I want to result has 1, 2, 3, 4, 5, 6, 7 items. In fact, that turns out to be Count A. You can use the Count A function of the Sweeping variable range. But actually before we do that, we need to use the indirect function because we want to let Excel know that we're actually using the named range sweeping that we defined before. Count A of our indirect of Cell E3 will give us the size, in this case, for our Sweeping array, that's going to be 1, 2, 3, 4, 5, 6, 7 rows and the width we want to be two. It's going to start in Cell A16, that's what we used in the offset. This will be a seven by two, and when I press Enter, we simply pluck out that sub array here for the Sweeping group. I can go ahead and resize this. Now the only thing that we need to do to compute the employee number of Stephanie in the sweeping group is to use the VLookup function. You could also use the Index Match Combination. But I'm looking for Stephanie in this table array. That pound sign there just represents that it's a spill. Then I can do the second column because I want to output the employee number and I'm going to put false for an exact match. When I press Enter, that's the employee number of Stephanie in the sweeping group. We can test this on a couple others. Cooking, you notice that we have an error here. I've got a Screencast that I referenced in the instructions for this project of how you can reset that, and you're going to have to reset that for this project. Meaning that Stephanie should have been reset to just a message that says please choose a name. You're going to have to do that for your project that requires some VBA code. This file is actually going to be a macro enabled workbook. But anyway, we can select Sally here and just make sure that that's the correct employee number, which it is. Now, if I wanted to instead of G8 here, the spill, which means this entire formula here, it's an array formula, I can just take that formula that I use Control, Copy, press Enter, and where I had G8, I can put that in there and press enter. Now I don't even need this, I'm just going to delete that. Then wherever I use cell H3 here, I can go in here and I can take the match function Control C, and I can go in here, and wherever I had H3, I can do Control V, press Enter, and now I don't even need the row, it's all embedded there. That's a nice clean project. This is an example of how we can do this multiple table lookups, and this is going to help you quite a bit in your Distance Calculator Project. You are going to have to do a couple more enhancements, but that's all in the requirements of the project. Thanks for watching and good luck.
