What is an equivalent for INDEX in ARRAYFORMULA?

google sheets array formula sum
array formula index match
google sheets arrayformula(query)
google sheets array formula vlookup
index match google sheets
vlookup google sheets
horizontal array formula google sheets
array formula row

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.

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.

google spreadsheet, 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  where "data" is the named range B5:B10.. How this formula works. It is surprisingly tricky to get INDEX to return more than one value to another function. To illustrate, the following formula can be used to return the first three items in the named range "data", when entered as a multi-cell array formula.

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

In a Google Sheet, using ARRAYFORMULA, INDEX/MATCH , In a Google Sheet, using ARRAYFORMULA, INDEX/MATCH formulas that were originally VLOOKUP formulas only take the first value in a column. unsolved. array_formula - A range, mathematical expression using one cell range or multiple ranges of the same size, or a function that returns a result greater than one cell. Notes Many array formulas will be automatically expanded into neighboring cells, obviating the explicit use of ARRAYFORMULA .

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

INDEX MATCH within ArrayFormula, We need to tell Google Sheets we want this to be an Array Formula. and includes that result in our output array (here showing the equivalent formulas): How can I find the color (column 4) by matching the date and the index value, say “B”? Either type in the word ArrayFormula and add an opening/closing brackets to wrap your formula, or, more easily, just hit Ctrl + Shift + Enter ( Cmd + Shift + Enter on a Mac) and Google Sheets will add the ArrayFormula wrapper for us. =ArrayFormula (B2:B5 * C2:C5) Now it works, and Google Sheets will output an array with each cell corresponding

How do array formulas work in Google Sheets? Get the lowdown here., Learn how to use INDEX MATCH in spreadsheets as an alternative for formula, press Ctrl+Shift+Enter to add ArrayFormula at the beginning. An array formula is a formula that can perform multiple calculations on one or more items in an array. You can think of an array as a row or column of values, or a combination of rows and columns of values. Array formulas can return either multiple results, or a single result.

INDEX MATCH in Google Sheets – another way for vertical lookup, The tutorial explains what the Excel INDEX function is and provides a Essentially, an INDEX formula returns a cell reference from within a given array so the second parameter of the INDEX function should by equal 1. I am using the following array formula {=INDEX(ASSETP,SMALL(IF(STATP=$A$10  For example, the formula CELL("width",INDEX(A1:B2,1,2)) is equivalent to CELL("width",B1). The CELL function uses the return value of INDEX as a cell reference. On the other hand, a formula such as 2*INDEX(A1:B2,1,2) translates the return value of INDEX into the number in cell B1.

Excel INDEX function with formula examples, 0 (zero) is equal to the first value in the array. Step 4 - Return value based on row number. The INDEX function returns a value from a given cell  Hello! Earlier I've created a formula in Excel which I now want to translate into DAX, but can't manage to figure out how. I have a table with all sales and these are some of colums: Assignment ID Assignment creation date and time Creator user ID What I want to create is a new column saying i

Comments
  • Possible duplicate of How to use INDEX() inside ARRAYFORMULA()?
  • 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!!!
  • 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?
  • 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).