Excel索引查找多个值 [英] Excel Index to look up multiple values

问题描述

我有一个由2列和几行组成的小型数据集(A和B列)

I have a small data set of 2 columns and several rows (columns A and B)

我想在工作表中其他位置的公式中返回codeblk 3的每个实例，(因此，vlookup退出，因为它仅显示第一个实例)，如果它不出现，则出现一条消息，指出它不在该位置来.

I want to return each instance of codeblk 3 in a formula that is elsewhere in my sheet, (so a vlookup is out as it only shows the first instance) if it does not appear then a message to say its not there should come up.

我的公式部分起作用，但是我看不到其不显示值的原因.

I have the formula partially working but i cant see the reason why its not displaying the values.

我的公式如下: 这是一个数组

{=IF(ISERROR(INDEX($A$55:$B$70,SMALL(IF($B$55:$B$70=3,ROW($B$55:$B$70)),ROW(1:1))-1,1)),"No value's produced",INDEX($A$2:$C$7,SMALL(IF($B$55:$B$70=3,ROW($B$55:$B$70)),ROW(1:1))-1,1))}

显示的结果仅为未生成值"，但它应在3个单独的单元格中反映语句B，C和D(更改ROW(1:1)，ROW(2:2)等时)

The result that shows up is only "No values produced" but it should reflect statement B, C and D in 3 separate cells (when changing ROW(1:1), ROW(2:2) etc)

{=SMALL(IF($B$56:$B$69=4,ROW($B$56:$B$69)),ROW(1:1))}-这将产生结果68，它是正确的行.

{=SMALL(IF($B$56:$B$69=4,ROW($B$56:$B$69)),ROW(1:1))} - This produces the result 68 which is the correct row.

有什么想法吗? 谢谢，

Any ideas? Thanks,

推荐答案

这是一个数组公式-使用 Ctrl + Shift + Enter ，同时仍在编辑栏中

This is an array formula - Validate the formula with Ctrl+Shift+Enter while still in the formula bar

=IFERROR(INDEX($A$55:$B$70,SMALL(IF($B$55:$B$70=3,ROW($B$55:$B$70)-54),ROW(1:1)),1),"No value's produced")

您面临的问题是索引从$ B $ 55的第一行开始，您需要偏移数组中的行号以反映这一点.例如，INDEX包含16行，但是如果您在第一行中有一个匹配项，则要求从该INDEX()中获取第55行，那么它就不能满足要求.

The issue you are facing is that your index starts it's first row on $B$55, you need to offset the row numbers in the array to reflect this. For example, the INDEX contains 16 rows but if you had a match on the first row you are asking for the 55th row from that INDEX(), it just can't fulfil that.

编辑

偏移量不同步，因为您的原始公式在IF()之外包括了另一个-1，我还留了一个额外的括号(上面的公式现已编辑)

The offset was out of sync as your original formula included another -1 outside of the IF(), I also left an additional bracket in play (the formula above has now been edited)

ROW()函数实际上会将$ B $ 55:$ B $ 70转换为ROW(55:70)，这将生成数组{55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70}，因此需要偏移量才能将这些行号转换为它们在索引数据中表示的位置INDEX().

The ROW() function will essentially translate $B$55:$B$70 into ROW(55:70) which will produce the array {55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70} so the offset is needed to translate those row numbers in to the position they represent in the indexed data of INDEX().

然后，另一个IF()语句生成{FALSE;2;3;4;FALSE等的数组.

The other IF() statement then produces and array of {FALSE;2;3;4;FALSE etc.

通过在公式栏中突出显示部分公式并单击F9进行计算，可以查看这些结果.

You can see these results by highlighting parts of the formula in the formula bar and hitting F9 to calculate.

这篇关于Excel索引查找多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！