![]() It also explains that 20 years later, my fascination for maps and grids has not changed. This should give you a basic understanding of how the INDEX function works. You can see that the column and row numbers are specific to the array range, NOT the worksheet. Therefore, the row and column arguments in the formula would remain 5 and 3. The price for the Caffe Mocha size Grande would still be in the 5 th row and 3 rd column of the array range. If however, the array range started in cell B2, then the array range would be $B$2:$E$11. The array range in our formula starts in cell A1, so the column and row number of the array will be the same as the column and row number of the worksheet. This is NOT necessarily the row and column number of the worksheet. In the example above we used row 5 and column 3 in the INDEX function. One important thing to note is that the row and column numbers we used in the INDEX function are relative to the array range ($A$1:$D$10). Row and Column Numbers are Relative to the Array Range You just tell it where you want to look (the array), and then give it the coordinates (the row & col numbers) of the cell value you want to return. I hope this helps explain how simple the INDEX function is. This is the value of cell C5 in the worksheet. The INDEX formula returns the result: $3.95. So we can put a 3 in the third argument of the function. We know that we want to return a price from the size Grande column, which is in column 3 of the array. ![]() column_num – This is the column number of the array that we want to return the result from. Since Caffe Mocha is in row 5 of the array, we simply put a 5 in the second argument of the formula.ģ. In our example, we want to return a result from row 5. row_num – This is the row number of the array that we want to return the result from. The next two arguments are the coordinates.Ģ. ![]() We first need to tell INDEX where we are going to look, then zoom in on the exact coordinates. ![]() To me this is very similar to the page in the map book. This argument tells the INDEX where we want to look in the spreadsheet. “What is the price of the Caffe Mocha, size Grande?” This time we will use the INDEX function to answer the same question from the VLOOKUP example. Let’s look back at our Starbucks menu example to learn about the arguments in the INDEX function. There are three arguments to the INDEX function. The INDEX function returns the value of a cell based on the grid coordinates you provide it. So we are just going to start with the basics of how the INDEX function works. I believe it has a reputation for being difficult and confusing because you have to combine it with a MATCH function to use it as a lookup formula. Its job is to return a cell value based on the grid coordinates that you feed it. The INDEX function in Excel is very similar to this. You would first have to find the coordinates of your destination in the index of the map book, then locate the page and grid square (cell) that your destination was in. The old road maps were laid out in a grid with columns and rows, just like an Excel spreadsheet. My parents always let me be the “navigator” on long road trips, and my job was to figure out where we were going and follow our course on the map. The INDEX function always reminds me of reading a road map.Īs a kid, I had a fascination with maps. In this third post I will explain the INDEX function.The second post explained how to make lookup formulas more dynamic with the VLOOKUP & MATCH functions.In the first post I explained the VLOOKUP function at Starbucks. ![]() This is the third post in a series about the most commonly used lookup functions in Excel. It is best to understand the basics of INDEX before creating more advanced formulas using INDEX & MATCH. This post will just focus on explaining the how the INDEX function works. The INDEX function is commonly used in combination with the MATCH function to provide powerful and dynamic lookup formulas. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |