Excel function: index

index() returns the nth value in array.

Get nth value in array

In the following example, the function testdata fills the values into the first and third column. Then, the function =index() is used in the fourth column to select the textual representation of the first column depdening on the numerical value in the third column.

option explicit

sub main() ' {

    testData

    range(cells(1,4), cells(5,4)).formulaR1C1 = "=index(r1c1:r5c1,rc[-1])"
end sub ' }

sub testData() ' {

 '
 '  Clear active sheet's data:
 '
    activeSheet.usedRange.clearContents

    cells(1, 1) = "one"
    cells(2, 1) = "two"
    cells(3, 1) = "three"
    cells(4, 1) = "four"
    cells(5, 1) = "five"

    cells(1, 3) = 4
    cells(2, 3) = 2
    cells(3, 3) = 4
    cells(4, 3) = 1
    cells(5, 3) = 2

end sub ' }

Github repository about-Excel, path: /functions/index/row.bas

Access specific row and column

index can also be used to access a specific row and column in a range:

option explicit

sub main() ' {

    testData

  '
  ' Get the value of the 4th row and 2nd column
  ' in the specified range (r2c2 to r6c4), thus
  ' evaluating to "5/3"
  '
    cells(2, 6).formulaR1C1 = "=index(r2c2:r6c4, 4, 2)"

end sub ' }

sub testData() ' {


    activeSheet.usedRange.clearContents

    dim r as long
    dim c as long

    for r = 2 to 6
    for c = 2 to 4
      '
      ' Specify the cell as text (although called numberFormat...)
      '
        cells(r, c).numberFormat = "@"

      '
      ' Set cell's text to row slash column.
      '
        cells(r, c) = r & "/" & c
    next c
    next r

end sub ' }

Github repository about-Excel, path: /functions/index/row-column.bas

Using a hardcoded array

The array to choose values from can also be hardcoded with curly braces.

option explicit

sub main() ' {

    cells(2, 2).formula = "=index({""one""; ""two""; ""three""; ""four""; ""five""}, a1)"
    cells(1, 1).value   =  4

  '
  ' It seems that index with a hardcoded array does not
  ' get updated automatically.
  '
    activeSheet.calculate

end sub ' }

Github repository about-Excel, path: /functions/index/hardcoded-array.bas

Apparently, when changing the index to a hardcoded array, the worksheet (or at least the the formula) needs to be recalculated.

Excel function: index

Get nth value in array

Access specific row and column

Using a hardcoded array

See also