Index / Match with multiple possible results.

New Contributor

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:


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

To pull the serial number:


             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 NumberDescriptionSerial NumberSerial Number Installed On
42BC99Exhaust ValveANY3432SGWNY9090
42BC99Exhaust ValveANY3234TGWNY9090
42BC99Exhaust ValveANYNY324GWNY9090
42BC99Exhaust ValveANY5B747GWNY9090
4 Replies


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?

best response confirmed by S_M_Howard (New Contributor)
I am using 365 and updated the original post with an example of the data I'm pulling against.

Thank you,




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