14

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.

Community
  • 1
  • 1
Cata
  • 478
  • 1
  • 5
  • 13
  • Possible duplicate of [How to use INDEX() inside ARRAYFORMULA()?](https://stackoverflow.com/questions/16140642/how-to-use-index-inside-arrayformula) – Rubén Jul 01 '18 at 21:26

4 Answers4

14

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.

  • 1
    Yes, I see vlookup being recommended in this case but in my understanding you need to know the value you are looking up, no? Well I don't know what value I am looking up, this is why I am looking it up!!! – Cata Jan 07 '17 at 22:26
  • That works, thanks! Good trick. Feels a little wasteful though to search through that entire first column for each lookup when a simple index-based addressing would retrieve the answer immediately. Should I worry about it? Is there any other faster way? – Cata Jan 07 '17 at 23:02
  • 1
    I wouldn't worry about. By default (without the 4th parameter being False), `vlookup` assumes that the set of keys is sorted and performs binary search, log(n). –  Jan 07 '17 at 23:05
1

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

Brendan Wilding
  • 51
  • 5
1

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

Luigi
  • 11
  • 1
0

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.
Max 63rus
  • 1
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-ask). – Community Sep 14 '21 at 15:52