Search multiple columns and output into another column

I am trying to make a formula that searches certain columns and returns an answer in another column. Please see attached test sheet. I have added a before and after tab to show what I am trying to achieve.

It is data coming from a form and in the long term the idea is to hide all the car models and have just one column for models but will be a lot easier if there was a formula that could do this.

Thanks,
Leaf

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.