Index / Match with multiple possible results.

[S_M_Howard]

I have a database of part number, name, serial number and the same information for the part they are installed on.  I'm using an Index / Match setup to pull specifics out of the database but I have one part that exists four (4) times on an engine, all with the same name, same part number but different serial numbers.  If I try to do the Index / Match I end up with (as we all know) the first item it finds in the list four times... how do I get the information for items 2, 3 and 4 in the inquiry?

To pull the part number:

=INDEX(Data!A:A,MATCH(1,INDEX((Data!D:D=Query!$F$7)*(Data!B:B=Query!B8),,),0),0)

           part # list                       part it is installed on                name of the individual part

To pull the serial number:

=INDEX(Data!C:C,MATCH(1,INDEX((Data!D:D=Query!$F$7)*(Data!A:A=Query!E8),,),0),0)

             serial # list                    part it is installed on               part # of the individual part

What can I add to these to grab the other items part number and serial number

As requested here is an example and yes the only thing that is different in the four items is the serial number:

Part Number	Description	Serial Number	Serial Number Installed On
95A754B2	Manifold	GWNY9090	75DSP2501
42BC99	Exhaust Valve	ANY3432S	GWNY9090
42BC99	Exhaust Valve	ANY3234T	GWNY9090
42BC99	Exhaust Valve	ANYNY324	GWNY9090
42BC99	Exhaust Valve	ANY5B747	GWNY9090

[#2]

@S_M_Howard 

Which version of Excel do you have?

And could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#3 · Accepted solution]

I am using 365 and updated the original post with an example of the data I'm pulling against.

Thank you,

[#4]

@S_M_Howard 

=FILTER(Data!A:A,(Data!D:D=Query!$F$7)*(Data!B:B=Query!B8),"")

=FILTER(Data!C:C,(Data!D:D=Query!$F$7)*(Data!A:A=Query!E8),"")

[#5]

Thank you Hans... These equations work beautifully!!!