google-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup

What is an equivalent for INDEX in ARRAYFORMULA?

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

  • 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.