将 Excel 数组函数嵌套在非数组函数中并返回数组

将 Excel 数组函数嵌套在非数组函数中并返回数组

我正在尝试创建一个电子表格,用于向客户提供报价。有些零件编号适用于单个项目。有些零件编号是最多 4 个项目的捆绑包。我正在尝试创建一个公式,返回与给定零件编号相关的所有值。

最初,我在报价中有两个部分 - 一个使用 VLOOKUP 返回包含单个项目的零件编号,另一个使用返回项目数组的数组公式。

第一个公式是

=IF(ISNA(VLOOKUP(B12,PriceList,2,FALSE)),"",VLOOKUP(B12,PriceList,2,FALSE))

第二个是

{=IFERROR(INDEX(Bundles!$B$2:$B$101, SMALL(IF($B$33=Bundles!$A$2:$A$101, ROW(Bundles!$B$2:$B$101 )  - 1,""), ROW() - 32 )),"")}

两者都可以独立运行。它们依赖于两个数据表PriceListBundles

前两个公式的结果:

前两个公式的结果截图

我希望销售代表能够在 B 列中输入零件编号并获取正确的零件描述 - 无论是 1、2、3 还是 4 项 - 显示在 C 列中。我希望他们能够在同一个报价中输入多个零件编号。

我尝试根据零件编号得出这个结论

=IF(LEFT(B29,4)="BUND",IFERROR(INDEX(Bundles!$B$2:$B$101,SMALL(IF($B$29=Bundles!$A$2:$A$101,ROW(Bundles!$B$2:$B$101)-1,""),ROW()-28)),""),VLOOKUP(B29,PriceList,2,FALSE))}

这对于捆绑物品有效,但会重复单个物品。

我想要有一个单一数据源(价目表)和一个单一公式。

数据源中的零件编号:

数据源中的零件编号

我现在尝试使用 COUNTIF。例如,如果 COUNTIF 返回大于 1,则使用数组公式,否则使用 VLOOKUP 公式。

我想象它就像

IF((COUNTIF(PriceList,Quote!B11)>1),"BUNDLE",IF(ISNA(VLOOKUP(Quote!B11,PriceList,2,FALSE)),"",VLOOKUP(Quote!B11,PriceList,2,FALSE)))

其中“BUNDLE”被数组函数替换。我似乎想不出正确的数组公式。

我试过

{=IF((COUNTIF(PriceList,Quote!B11)>1),IFERROR(INDEX(Bundles!$B$2:$B$101, SMALL(IF($B$11=Bundles!$A$2:$A$101, ROW(Bundles!$B$2:$B$101 )  - 1,""), ROW() - 32 )),""),IF(ISNA(VLOOKUP(Quote!B11,PriceList,2,FALSE)),"",VLOOKUP(Quote!B11,PriceList,2,FALSE)))}

对于单件商品,这将返回四行相同商品,而对于捆绑商品,则不返回任何内容

我曾考虑过将数组函数放在另一个单元格并引用该单元格,但如果捆绑包包含多个项目,这无济于事。

欢迎任何想法或建议。

相关内容