Situatie
When you need to obtain a value that resides in a particular spot in your spreadsheet, you’ll want one of Excel’s lookup and reference functions. Here, we’ll show you how to find values by location with INDEX in Excel.
You might be creating a complex formula or have values that change often. By using the INDEX function, you designate the cell range along with a row number and column number. Excel locates the value at that intersection and provides the result from the formula.
Solutie
The INDEX Function in Excel
You can use the INDEX function two different ways in Excel: Array Form and Reference Form.
Array Form provides the value of a certain cell range, or array. Reference Form provides a reference to specific cells and is useful when working with nonadjacent cells.
- Use INDEX in Array Form
The syntax for the function in Array Form is INDEX(array, row_number, column_number) where the first two arguments are required and column_number is optional.
To find the value in the third row in the cell range C1 through C10, you would enter the following formula replacing the cell references with your own.
=INDEX(C1:C10,3)
The number 3 here represents the third row. You don’t need the column number argument because the array is in a single column, C.
To find the value in the third row and fifth column for the cell range A1 through E10, you would use this formula.
=INDEX(A1:E10,3,5)
Here, the 3 represents the third row and the 5 represents the fifth column. Because the array covers several columns, you should include the column number argument.
Leave A Comment?