索引/匹配多个工作表中的多个结果 [英] Index/Match multiple results in multiple sheets

问题描述

正如标题所示，我希望在多个表中进行索引/匹配，但还要返回多个结果.

As the title suggests I'm looking to do an Index/Match in multiples sheets but to also return multiple results.

我知道如何返回多个结果

I know how to return multiple results with

=IFERROR(INDEX(Sheet1!B$1:B$100,SMALL(IF(Sheet1!$A$1:$A$100=myValue,ROW(Sheet!$A$1:$A$100)),ROWS(1:1))),"")

然后将其复制下来.

我还知道如何浏览多个表以获取唯一值

I also know how to look through multiple sheets for a unique value

=IFERROR(INDEX(Sheet1!B$1:B$100,MATCH(myValue,Sheet1!$A$1:$A$100,0)),INDEX(Sheet2!B$1:B$100,MATCH(myValue,Sheet2!$A$1:$A$100,0)))

我想知道是否有一种方法可以处理多个工作表中的多个值?以某种方式将两个公式结合起来?

I was wondering if there was a way to do this with multiple values in multiple sheets ? Combining the two formulas in some way ?

我的问题是，因为我不知道何时在搜索中进行两张纸之间的切换，所以我无法用第一个公式中的正确行数来抵消它.

My problem is that because I do not know when the switch between the two sheets will be made in the search, I cannot offset it by the correct number of rows in the first formula.

谢谢大家！

推荐答案

感谢吉普(Jeeped)，正是这样！只需计算其他表中的匹配项即可重置行.

Thanks Jeeped, it was exactly that ! Just needed to count the matched in the other sheets to reset the rows.

最终公式如下

=IFERROR(IFERROR(INDEX(Sheet1!B$1:B$100,SMALL(IF(Sheet1!$A$1:$A$100=myValue,ROW(Sheet!$A$1:$A$100)),ROWS(1:1))),INDEX(Sheet2!B$1:B$100,SMALL(IF(Sheet2!$A$1:$A$100=myValue,ROW(Sheet!$A$1:$A$100)),ROWS(1:1)-COUNTIF(Sheet1!$A$1:$A$100,myValue))),"")

这篇关于索引/匹配多个工作表中的多个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！