What is an equivalent for INDEX in ARRAYFORMULA?

Question

I have (what I thought was) a simple lookup table holding some exchange rates. There I am looking up values based on row and column indices.

How can I do that in an ARRAYFORMULA like this?:

=ARRAYFORMULA(INDEX(x!C2:C, F1:F))

x is the sheet holding the values, I am interested in column C, row index being held in my column F. Single value lookup like INDEX(x!C2:C, F7) is working as expected.

But the ARRAYFORMULA sadly it's not working since INDEX is not supported in ARRAYFORMULA I guess.

Answer 1

vlookup can be adapted to mimic index functionality by making the first column of the lookup table the row number (with a suitable offset). Example:

=arrayformula(vlookup(F1:F, {row(x!C2:C) - row(x!C2) + 1, x!C2:C}, 2))

does what you tried to do with "=ARRAYFORMULA(INDEX(x!C2:C, F1:F))".

The lookup table {row(x!C2:C) - row(x!C2) + 1, x!C2:C} has first column 1,2,3,... and the second column the range you wish to index. So, for each value from F1:F, vlookup accesses the entry of x!C2:C that index would.

Answer 2

Please forgive me for my English. It is possible to use VLOOKUP in almost the same way as INDEX. =IFERROR( ARRAYFORMULA(VLOOKUP(ARRAYFORMULA((F1:F1000));({ARRAYFORMULA(ROW(x!C2:C1000))(x!C2:C1000)});2;0));"")

the main thing is to limit the range from and to, so that the arrayformula does not loop

1. For VLOOKUP, search_key is ARRAYFORMULA ((F1:F1000) - range array.
2. For VLOOKUP range -({ARRAYFORMULA(ROW (x!C2:C1000))(x!C2:C1000)})

• generate our table from 2 columns, the first column is " ARRAYFORMULA(ROW(x!C2:C1000))", the second column (x!C2:C1000). the " \ " sign is required to write to the array not in 1 column but in 2.

Answer 3

You can write a custom script to do this, which can then be used in place of the regular index() function. Just do to Tools --> Script editor then paste in the code, save, then you can use the function like a normal function in Google sheets.

Code:

function INDEXMULTI(array, rows, columns) {
  var ret = new Array;
  var i;
  if (rows[0].length != columns[0].length)
    return "Error: Row and column count must be the same";
  for (i=0; i<rows[0].length; i++)
    ret.push(array[(rows[0][i]-1)][(columns[0][i]-1)]);
  return ret;
}

This function takes the array you want to extract the data from as the first argument and then the rows and columns of the data to be extracted as the second and third arguments. The [0] in the code are just to extract the values from 1-D arrays and the -1 are because javascript arrays are zero based whereas Google sheets is 1 based.

You can see a demo of it here: https://docs.google.com/spreadsheets/d/1o6uiRr_OKh6lOUY4pOp_5z7hAIzGifFaXUIMOO7SCoc/edit#gid=0

Answer 4

you can replace index by Vlookup by writing "Anything" in x!C1 and use : =ARRAYFORMULA(Vlookup("Anything", TRANSPOSE(x!C2:C),F1:F))