循环多个查找的函数 [英] Looping a function for multiple lookups

问题描述

我正在尝试编写一个在不同工作表中搜索零件编号的公式，并导入零件检查失败的所有时间。问题是，当一个部件失败时，我有多种情况，所以它们分成不同的行。现在我可以使用以下公式调用一个实例：

I'm trying to write a formula that search for a part number in a different sheet, and imports all of the times the part had failed inspection. The problem is that I have multiple cases when a part failed, so they fall into different rows. Right now I am able to call up one instance using the following formula;

=INDEX('QN Data'!$A3:$A10000,MATCH($B$4,'QN Data'!$D$3:$D$10000,0))

我需要转移共有6种不同的事件。现在有没有办法让excel记住它在哪里停下来，从那里开始？

There are a total of 6 different occurrences that I need transferred. Now is there a way for excel to remember where it left off on and start from there?

我也不能使用任何宏。我们目前有一个这样做，但我的老板不喜欢使用它们。我尝试填写公式，但它只是给我的QN号码，在我想要的零件号下面的QN号码是完全不同的零件号。

Also I cannot use any macros. We currently have one that does all of this, but my boss does not like using them. I tried filling the formula down but it just gives me the QN number that is below the one for the part number I want which is a different part number completely.

推荐答案

您可以使用 AGGREGATE功能强制任何不匹配的错误，忽略错误。

You can retrieve a multiple match using the AGGREGATE function to force anything that does not match into an error and ignore the errors.

=INDEX('QN Data'!$A$3:$A$10000, AGGREGATE(15, 6, ROW($1:$9998)/('QN Data'!$D$3:$D$10000=$B$4), ROW(1:1)))

您实际上正在使用小的AGGREGATE功能的子功能，所以你可以得到第二，第三等等，通过增加 k 参数进行连续匹配。我通过使用等于1的 ROW（1：1）完成上述操作，但会随着公式的填充而增加到2,3等。

You are actually using the SMALL sub-function of the AGGREGATE function so you can get the second, third, etc. successive matches by increasing the k paramter. I done this above by using ROW(1:1) which equals 1 but will increase to 2, 3, etc as the formula is filled down.

中的相对行位置'QN Data'！$ A $ 3：$ A $ 10000通过强制任何不符合＃DIV / 0！错误状态。 6 选项告诉AGGREGATE忽略错误。

The relative row position within 'QN Data'!$A$3:$A$10000 is returned by forcing any row position that does not match into a #DIV/0! error state. The 6 option tells AGGREGATE to ignore errors.

您可能希望放置一个 IFERROR函数，以便您可以填充更多的行而不显示 #NUM！当您的比赛用完时出现错误。

You may wish to put an IFERROR function around the formula so that you can fill down for more rows than necessary without showing #NUM! errors when you run out of matches.

这篇关于循环多个查找的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！