使用INDEX数组公式返回多个值 [英] Returning multiple values with INDEX array formula

问题描述

我已经用这个数组公式进行了多次尝试，但到目前为止没有运气。所有发布的解决方案看似相似，但没有一个工作。

这是我的工作表：

I have made multiple attempts with this array formula but have had no luck so far. The solutions that are posted all seem similar but none have worked yet.

Here is my worksheet: https://drive.google.com/file/d/0B2SzIXxoU3dYSkYzZWU1U1dvbVE/view?usp=sharing

As you can see I am trying to pull the names of firms from the RVR tab into an array on the FPP-Vendors tab if they meet two criteria (FPP column has an "X" and result column has a "pass"). So far I can only get the array formula to return the name of Firm A, but it should pull Firm A and Firm C (and skip over Firm B, that does not have a "pass" in the result column). The named ranges in my array formula are listed on the RVR tab.

解决方案

Adjust your ranges as necessary and enter with CTRL + SHIFT + ENTER:

=IFERROR(INDEX($B$2:$B$4,SMALL(IF($C$2:$C$4="X",IF($D$2:$D$4="Pass",ROW($A$2:$A$4)-ROW($A$2)+1)),ROWS($A$2:A2))),"")

这篇关于使用INDEX数组公式返回多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！