Here, the array is the entire "Total" column and row number is 1. This works because, the INDEX function returns a reference to the first cell, not the actual value.
To get a reference to the current row, we use:
This is the standard structured reference syntax for "this row".
As the formula is copied down the column, the reference to the first cell doesn't change, but the referent to the current cell changes at each row. The result is a reference that expands. The SUM function sums the values in the range at each row, creating a running total.
To do a two-way lookup in an Excel Table , you can use the MATCH function with a structured reference and VLOOKUP. In the example shown, the formula in I5 (copied down) is: = VLOOKUP ( $I$4 , Table1 , MATCH ( H5 , Table1 [ #Headers ], 0 ), 0 ) How...
The Excel INDEX function returns the value at a given position in a range or array. You can use INDEX to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to...
The Excel SUM function returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments.
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.