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 功能，方法是将查找表的第一列设为行号(具有合适的偏移量).示例:

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))

使用=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屋！