使用一次查找生成多个值 [英] Use one lookup to generate multiple values
问题描述
我有一个数组,其中包含具有多个产品的产品订单.
I have an array filled with product orders that have multiple products.
Order # Product
N-122 C135
N-122 P223
N-122 C134
N-134 P223
我已经看到其他线程将产品合并为具有相同顺序的一行.
Excel宏-逗号分隔单元格的行(保留/汇总)列)
I have seen other threads merge products into one row that have the same order.
Excel Macro - Rows to Comma Separated Cells (Preserve/Aggregate Column)
是否可以在不将产品合并为一行的情况下查找我的订单值?
Is there a way to lookup my order values without merging the products into one row?
我想在另一个工作表中创建一个表,一旦输入订单号,它将在多行中显示与之关联的所有产品.
I want to create a table in another sheet that once the Order # is input it will show all of the products associated with it on multiple rows.
Order # Product
N-122 C135
P223
C134
推荐答案
正如安德烈(Andre)提到的,您可以使用数据透视表,但是如果您仍想执行公式,则可以执行此操作
As Andre mentioned you can use a Pivot Table but if you still want to do a formula then you can do this
= IFERROR(INDEX($ A $ 2:$ B $ 5,SMALL(IF($ A $ 2:$ A $ 5 = $ D $ 1,ROW($ A $ 2:$ A $ 5)-1),ROW(A1)),2),")
请注意,这是一个数组公式,您必须按 CTRL + SHIFT + ENTER
Note this is an Array formula and you have to press CTRL+SHIFT+ENTER
查看此屏幕截图
在 E1
中输入公式后,只需将其向下拖动即可找到匹配项.
Once you enter the formula in E1
, simply drag it down to find matches.
这篇关于使用一次查找生成多个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!