Video instructions and help with filling out and completing Who Form 2220 Columns

Instructions and Help about Who Form 2220 Columns

Music Applause Music hello everyone and welcome to the Excel challenge in this video I'm going to show an easy way to find the intersection of two values in a table for this example I'm using the driving distance between US cities I have 15 cities on the Left I have 15 cities on the top and let's say you want to find the driving distance between Miami and Tampa the distance between Miami and Tampa is 329 miles if you want to see how to build as a spreadsheet please stay tuned okay the first thing we want to do is prepare our database the database I prepare for this example is a simple table of 15 US cities that we can see in these 15 rows and the same 15 cities in this 15 columns if I want to know the distance from Chicago to Dallas I can see the distance is twelve hundred and ninety miles same idea Denver to Miami 27 hundred and seventy two miles now the key to resolve this challenge is to understand rows and columns what I'm going to do next is temporarily write a row number for each of the 15 cities on the left and a column number for each of the 15 cities on the top the challenge today is to identify what is the distance from two let's say what is the distance from Chicago to Houston first thing we want to know is identify the row number of this intersection by the way the intersection here is Chicago's in Row 3 Houston is in column 6 row number column number and the value in the intersection in order to understand the row number we're gonna use the match formula so I'm gonna tell Excel match open parenthesis the lookup value is gonna be the city that I'm driving from comma and my lookup array or my lookup branch is gonna be the rows on the Left comma XLS ask me what kind of match I want of course they want to expect much close parenthesis enter so now I know that this city is in row number 3 let's use the same formula for our column the match value I want to look up is the CDM driving to comma I want to find this value in this range of columns comma and I want an exact match as well which is a 0 those parentheses enter so I know my my intersection is in Row 3 column 6 the next thing I'm going to do in order to pull the value in intersection is to use the formula index index is going to ask me what array or range of data you want to use this is the range of data that I want to use comma when he asked me for the roll number I point at this cell which contains a roll number comma when you asked me for the I pointed this cell which has the column number I'm looking for close parentheses enter and there we have it the distance between Chicago and Houston is fifteen hundred and ten miles one last thing we're gonna do is for our answer here and throw that we're going to use the formula concatenate concatenate merges strings of text and values so let's use calm tonight open parenthesis the answer is the distance between comma the value in the cell which is Chicago comma and comma the value in this cell which in this case is Houston comma is comma the value in the intersection which is fifteen hundred and ten miles comma miles let's see what it looks like close parenthesis enter what is the distance between Chicago and Houston the distance between Chicago and Houston is fifteen hundred and ten miles now the next thing we want to do is insert the drop down menu so that we don't have to be typing the values in the cell we're going to go to the data tab and in the data validation many we're gonna say data validation we want a list and we want this wrench in the list okay now I hope I drop down menu same idea for the to city data validation I want a list in the range I want is the columns on the top okay so and I can quickly play with the city that I want to go from and the city I want to go to and again I get the answer in this case the distance from Tampa to Chicago is sixteen hundred and fourteen miles and finally the last thing we're going to do is remove the redundancy in the intersection formula we have here is index we have the value in cell b7 which is this and the value in cell b8 b7 contains this formula so what I'm going to do is copy the formula in b7 and I'm going to replace this b7 with it enter I no longer need this formulas here same idea with the value I have in cell b8 I'm gonna copy and replace this b8 with my formula enter I no longer need this value now this formula is intersection between the two so if I copy this whole formula and paste into my concatenate formula I no longer need the value in cell b9 enter I delete all this and I have my answer what is the distance from New York to Philadelphia the distance from New York and Philadelphia is 129 miles now it's time for a bonus trick I no longer need these values here and I no longer need these values here now I'm going to show you how to use conditional formatting to make the intersection of the two values more visual we're going to put a condition over here if I go to the Home tab conditional formatting and I want a new rule the new rule is