回到索引和匹配多个值，而不VBA(Returning multiple values with IN

当我看到这个题目回答之前，我似乎无法理解解决方案:(

这是我的工作表： https://docs.google.com/spreadsheet/pub?key=0AsCQyX3EZ40SdC1FNFBjVDh6d01iY2g0WnVXOU5GeFE&output=xls

正如你可以看到我还需要第二次INDEX在第一片材返回第二个值看，而是（如预期），这再次显示了第一个。

我不是最好的与Excel，慢慢解释，我就明白了快！

提前致谢！

Answer 1:

尝试在计算器片单元格A3这个“数组公式”

=IFERROR(INDEX(IngredientDB!B$1:B$100,SMALL(IF(IngredientDB!$A$1:$A$100=$B$1,ROW(IngredientDB!$A$1:$A$100)),ROWS(A$3:A3))),"")

使用Ctrl + Shift + Enter确认，跨，下复制。 当你用完条目你空白 - 如需要承担高达100行的数据，增加了

Answer 2:

If you wanted to go the pivot table route you can start with this as a base and then customize it to your exact liking.:

Start with your info:

Then add a pivot of your data:

Then set the properties as so and then you can select the search terms, you can also change the settings to allow someone to type it in also:

The result will be as so:

文章来源: Returning multiple values with INDEX and MATCH without VBA