Looping a function for multiple lookups

Question

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))

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?

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.

Answer 1

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)))

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.

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.

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.