INDEX is a powerful and versatile function. Array is the range or array from which to retrieve values. In the example shown above, the goal is to get the diameter of the planet Jupiter. Because Jupiter is the fifth planet in the list, and Diameter is the third column, the formula in G7 is:. The formula above is of limited value because the row number and column number have been hard-coded. INDEX gets a value at a given location in a range of cells based on numeric position. When the range is one-dimensional, you only need to supply a row number.
When the range is two-dimensional, you'll need to supply both the row and column number. For example, to get the third item from the one-dimensional range A1:A In the examples above, the position is "hardcoded".
The formula in G7 is:. In the screen below, the table above has been transposed horizontally. The formula in C10 is:. This example shows a practical application of this idea. In a typical formula, you'll see the value in cell A2 as the result, so it's not obvious that INDEX is returning a reference.
However, this is a useful feature in formulas like this one , which uses INDEX to create a dynamic named range.
Both forms have the same behavior — INDEX returns a reference in an array based on a given row and column location. The difference is that the reference form of INDEX allows more than one array , along with an optional argument to select which array should be used. In the array form of INDEX, the first parameter is an array , which is supplied as a range of cells or an array constant. The first array inside reference is 1, the second array is 2, and so on.
Lookup values must appear in the first The areas listed here must all be located on one sheet. If you need to use ranges that are located on different sheets from each other, it is recommended that you use the array form of the INDEX function, and use another function to calculate the range that makes up the array. For formulas to show results, select them, press F2, and then press Enter. The intersection of the second row and third column in the range A2:C6, which is the contents of cell C3.
The intersection of the second row and second column in the second area of A8:C11, which is the contents of cell B9. The sum of the range starting at B2, and ending at the intersection of the fifth row and the second column of the range A2:A6, which is the sum of B2:B6. MATCH function. Guidelines and examples of array formulas. Lookup and reference functions reference. Formulas and functions.
Need more help? Expand your skills. Get new features first. Was this information helpful? Yes No. Thank you! Any more feedback? The more you tell us the more we can help. Can you help us improve? It can be used as a worksheet function WS in Excel. As a worksheet function, the INDEX function can be entered as part of a formula in a cell of a worksheet. Download Example. The first parameter in the INDEX function is the table or the source of data where the lookup should be performed.
In this example, the first parameter is A2:D6 which defines the range of cells that contains the data. The second parameter is the row number used to determine the intersection location in the table.
A value of 1 indicates the first row in the table, a value of 2 is the second row, and so on. In this example, the second parameter is 1 so we know that our intersection will occur in the first row in the table.
0コメント