How to Use INDEX Function in Excel
The INDEX formula is a great tool for finding out information from a predefined range of data. In our example, we’re going to use a list of orders from a fictional retailer that sells both stationary, and pet treats. Our order report includes order numbers, product names, their individual prices, and quantity sold. You can also use different cells for your Row and Column inputs to allow for dynamic INDEX outputs, without adjusting your original formula. That might look something like this: That outputs 15. The only difference here, is that the Row and Column data in the INDEX formula are input as cell references, in this case, F2, and G2. When the contents of those cells are adjusted, the INDEX output changes accordingly.
How to Use INDEX Function With Reference
You can also use the INDEX formula with a reference, instead of an array. This lets you define multiple ranges, or arrays, to draw data from. The function is input almost identically, but it utilizes one additional piece of information: the area number. That looks like this: We’ll use our original example database in much the same way to show what a reference INDEX function can do. But we will define three separate arrays within that range, enclosing them within a second set of brackets.
What Is the INDEX Formula in Excel?
The INDEX function is a formula within Excel and other databasing tools which grabs a value from a list or table based on the location data you enter into the formula. It is typically displayed in this format: What that’s doing is designating the INDEX function and giving it the parameters that you need it to draw the data from. It starts with the data range, or a named range that you have previously designated; followed by the relative row number of the array, and the relative column number. That means you’re inputting the row and column numbers within your designated range. So if you were to want to draw something from the second row in your data range, you would input 2 for the row number, even if it’s not the second row in the entire database. The same goes for the column input.
