I have had a look at your sheet and would love to help you out with your custom function!
I would like to make the following layout changes to your sheet though to ensure a more stable solution:
1. Would you open to be converting your filtered list to a proper data table? That way the forumlae will all automatically autofill down when you enter a new row (data tables will still have filters attached as per your sheet)
2. It would be a lot more robust solution if the part name and car columns were split, as I am assuming in practice your cars won't all be named 'Car 1', 'Car 2' etc. and it would be a bit more challenging to determine the car matches from a programmatic standpoint
3. Add a mapping table which orders the car condition (so New is best and Critical is the worst)
From there I would add two customer functions - one which returns the minimum overall condition for the car which is added to the 'Condition' column, and one which returns the parts which are in that condition.
About me: I am a technical business analyst with 15 years professional experience with Excel and VBA. I am detail orientated and have an eye for spotting patterns, a creative problem solving approach, and an excellent work ethic. I am passionate about Excel and small businesses - I love using tools which most people are familiar with to build solutions to save busy people time