在没有VBA的情况下使用INDEX和MATCH返回多个值 [英] Returning multiple values with INDEX and MATCH without VBA

问题描述

虽然我在似乎无法理解解决方案之前就已经看到了这个主题的答案:(

While i have seen this topic answered before i cant seem to understand the solution :(

这是我的工作表:

Here is my worksheet: https://docs.google.com/spreadsheet/pub?key=0AsCQyX3EZ40SdC1FNFBjVDh6d01iY2g0WnVXOU5GeFE&output=xls

如您所见，我需要第一张表中的第二个INDEX来返回看起来的第二个值，但是(如预期的那样)它再次显示第一个值.

As you can see i need the second INDEX in the first sheet to return the second value looked, but instead (as expected) it shows the first one again.

我不是使用excel的最好人，请慢慢解释，我会很快理解！

I am not the best with excel, explain slowly and i will understand fast!

提前谢谢！

推荐答案

在计算器工作表单元格A3中尝试使用此数组公式"

Try this "array formula" in Calculator sheet cell 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行数据，请根据需要增加

confirmed with CTRL+SHIFT+ENTER and copied across and down. When you run out of entries you get blanks - assumes up to 100 rows of data, increase as required

这篇关于在没有VBA的情况下使用INDEX和MATCH返回多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！