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.

Solution 1

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

Solution 2

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