相当于ARRAYFORMULA中的INDEX? [英] 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.

如何在这样的ARRAYFORMULA中做到这一点?:

How can I do that in an ARRAYFORMULA like this?:

=ARRAYFORMULA(INDEX(x!C2:C, F1:F))

x 是保存值的工作表，我对C列感兴趣，行索引保存在F列中.单个值查找如 INDEX(x！C2:C，F7)正常运行.

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.

但是可悲的是，ARRAYFORMULA无法正常工作，因为我猜ARRAYFORMULA不支持INDEX.

But the ARRAYFORMULA sadly it's not working since INDEX is not supported in ARRAYFORMULA I guess.

推荐答案

vlookup 可以通过将查找表的第一列设置为行号(具有适当的偏移量)来模仿 index 功能.示例:

=arrayformula(vlookup(F1:F, {row(x!C2:C) - row(x!C2) + 1, x!C2:C}, 2))

执行您尝试使用"= ARRAYFORMULA(INDEX(x！C2:C，F1:F)))"的操作.

does what you tried to do with "=ARRAYFORMULA(INDEX(x!C2:C, F1:F))".

查找表 {row(x！C2:C)-row(x！C2)+ 1，x！C2:C} 具有第一列1,2,3，...第二列是您希望索引的范围.因此，对于F1:F中的每个值，vlookup都会访问 index 将要输入的x！C2:C条目.

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.

这篇关于相当于ARRAYFORMULA中的INDEX?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！