Advanced VLOOKUP or Find & Replace

[ArbonneExcel]

I need to find information in the second column using information from the first and third columns. Referring to the table below, I need to find ALL tools that correspond to the part in column A, that are a part that is found in column C. I have tried a VLOOKUP to locate the value in the "Tool" column using C2 as the lookup_value, and cells $A$2:$B$10 as the table_array, 2 as the column_index_number, with FALSE for an exact match. This was copied down in column D, the lookup_value changing as it went down. This would return a value of 1R in cell D1. In cell D2, it also returns 1R, although I want it to also return HAMMER. Due to a large amount of data, I was wondering if there was any macro/functions that could return an array of values. IE if cell C2 was searched in column A, it would return an array that includes both 1R and HAMMER, but the amount of tools that a part has varies, so the array would have to be able to be different sizes. The second option I tried was if the value of C2 was found in column A, replace it with a value (such as "LL", but it could be anything as long as it doesn't match a part I'm not looking for) that is colored, such as purple. After this is done, I would sort the information in column A based on font color first, and then all the tools would be organized in column B that I need (the tools corresponding to the new parts only. Any help? P.S. The cells all contain a mixture of numbers/letters, such as 243U33-4. Sorry if this is confusing or explained poorly, first time on MrExcel, but I'm willing to answer any questions to help you understand better! Everything is appreciated.

---

All Parts	Tool	New Parts
A	1R	A
A	HAMMER	A
B	1R	A
B	2B	B
B	SCREW	D
C	HAMMER	E
C	1R	E
C	2B	G
C	24T	G

<tbody>
</tbody>