How to return a range of values based on Multiple Criteria

Hi Jamster and welcome to the forum,

I'd be trying a Filter In Place tool. You need to learn how to build a Criteria for an Advanced Filter first. Then I'd add a double click on what remained to do more filtering. Have you ever used the Advanced Filter Tool in the Data Tab?

OK Jamster,

I went crazy building you an app using double click and Advanced filter. You should take my attached file and copy and paste your real data into columns A through F. I've built some fake data to use and test my code.....

Here is what the code does.... It does an Advanced filter of your data in Columns A to F using a Criteria range in Columns H to M. The answer shows in Columns O through T
BUT I've added code to have you double click on what you want to filter by. So if you double click On Ford, it will only show Ford in the Filter Area. Now in either the original or filtered area you can double click on Mustang and it will only show "Ford and Mustang" in the filtered area. This is really cool if you do this a lot.

To clear the criteria area double click on Row 1 of the Criteria area (Columns H through M). To clear the Filter area double click on the top row of it .
Give this file a try with the fake data. Ask questions. Copy your read data into this workbook. Ask questions...
Parts AdvFilter on Double Click for Jamster.xlsm

OK Jamster,

I've added a new column with a formula to show "Year Range". All columns but Year must match and it will give the Min Year - Max Year.

See if this works to make your parts lookup faster.
Parts AdvFilter on Double Click for Jamster w Year Range.xlsm

To find parts for cars down on the list you might use the CTRL+F (Find Dialog). You might type in "Sprinter" and it might take you to row 7000. You would double click on the word and it will filter like it should. It might be helpful to have some dropdown lists in the criteria range of all possible car Makes. I'd need some better real data to build those dropdown lists.

Hope this works for you. I know how many auto parts there are and there are very few standards.

As to having the "range appear next to all rows" in columns A:F of Marvin's file (post #6)
1. Insert a new column G.
2. Select cell V2
3. Press the Ctrl + c keys
4. Select cell G2
5. Press the Ctrl + v keys
6. Double click the fill handle to copy down
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.