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.

Gatha

Possible duplicate of How to use INDEX() inside ARRAYFORMULA()? – Gangland

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.

Menorca

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!!! – Gatha

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? – Gatha

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). – Menorca

Answer 2

You can replace INDEX by VLOOKUP with the help of TRANSPOSE and use:

=ARRAYFORMULA(Vlookup("Anything", TRANSPOSE(x!C2:C),F1:F))

Madiemadigan

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

Ferriter

Answer 4

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.

Romain

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. – Embroideress

Answer 5

Here's a solution with no change in the original data needed:

=ARRAYFORMULA(VLOOKUP("*", TRANSPOSE(x!C2:C), F1:F, FALSE))

This should give the same result as

=ARRAYFORMULA(VLOOKUP("Anything", TRANSPOSE(x!C2:C), F1:F))

(slightly modified from: answered Jun 9, 2019 at 23:03 by user Luigi. In Luigi's Answer you actually also don't have to modify data. You don't have to write "Anything" in x!C1. The VLOOKUP with the default value is_sorted=TRUE should always bring the desired result, no matter what you hand in as search_key, as the lookup area consists of only one row after the transpose).

Nebulize