Cant get INDEX, MATCH to return more than one unique value.

Hi All!

I have a spread sheet where I type in a part number, and it returns the next part up in the bill of material. I have a formula that will get me the answer, however my problem is that sometimes there will be more than one answer. If one part makes more than one part, I will need the formula to find each unique value, but i cannot seem to get it correct. I have uploaded an example sheet so that you can see what I am talking about.

Thanks!
Codemancode

I used some named ranges to make the formula shorter / easier.
FGs refers to:

Please Login or Register  to view this content.

CPs refers to:

Please Login or Register  to view this content.

I used this formula in A10 and dragged it down to A11. It must be entered as an array formula with ctrl+shift+enter:

Please Login or Register  to view this content.

Awesome, thank you Sir! I wish i would remember to do named ranges more often,. Makes things so much easier...

You're welcome, and good luck. If you get ambitious, search for dynamic named ranges. Those make life really great when your ranges are constantly changing.