This means we can change the From and T0 cell values by using the drop down lists we used for data validtion in our data set up and the mileage will dynamicallt update. So in this example, let’s lookup the distance between 2x National Parks,įirst we use INDEX to set the array of where our data is to be found, we then need to set the row number and column numbers to use, which are provided by the two MATCH functions. So let’s get these powerful two functions working together and one HUGE advantage of the INDEX / MATCH functions is that the lookup value can be in any column in the array, unlike the VLOOKUP function, in which the lookup value must be in the first column – always, this gives great flexibility. You can also check out my blog post here on setting up custom data lists to enable a user to select from a list of data. In addition to having my data table as a named range, I also have my cities for selection for the start and end destination set up as a data validation list. So let’s get started and combine these two functions as a very powerful alternative to VLOOKUP.įirst of all here is my data set up again. Excel tells us the Death Valley is at position 2. You can see that B10 is our value, we want to find the position of, our lookup array is our list of National parks which is a nmed range of national_parks, we have used the match type 0 as we want to find an exact match. 1 – is this value is used then Excel will find the smallest value that is greater than or equal to valueĪgain let’s take a look at example of using MATCH and use it to find the position of Death Valley in the list in Column A?. If the match type is not entered then it defaults to 1.Ġ – if this is used then Excel will find the first value that is equal to value. Match_type – this is an optional argument with 3 possible values.ġ this is the default value and Excel will find the largest value that is less than or equal to value. Value – the value to search for in the arrayĪrray – this is the data set or range of cells that contains the value you are looking for MATCH can return the value of a position with in data list. Next the let’s look at the MATCH function. So we can see that Excel returns 534 which is the position of the cell intersection of row 3 and column 2. If you want 3 great reasons to use named ranges in your formulas then you can read my previous #formulafriday blog post here. The distance data contained in the table is a NAMED RANGE. I have a data table of distances between some National Parks in USA. Let’s take a look at an example and return the 3rd item in the 2nd column in my data set below? These Row and Column numbers cannot be BOTH left blank or zero. Note if this is left blank or se to zero then it defaults to all columns in the array and also this is the relative position of the column in the array of cells, not the column number on the worksheet in Excel. Note if this is left blank or se to zero then it defaults to all rows in the array and also this is the relative position of the row in the array of cells, not the row number on the worksheet in Excel.Ĭolumn_number – this is the column number or position of the cell you want to look up. Row_number – this is the row number or position of the cell you want to look up. =INDEX( array, row_number, column_number )Īrray – a range of cells that contains your data INDEX returns a reference to a cell based on the intersection/position of a specified row and column in a data table or an array of numbers or, to put it another way it returns an item from a specific location in a data list. Read on to find out why.įirst let’s get some theory out of the way by looking at the syntax of the functions individually. Well they are the bigger brother of the VLOOKUP. Today let’s look at a great at a couple of really flexible and powerful functions -especially together – the INDEX and the MATCH function.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |