Lookup

Maybe this

Enter formula in E2 and copy down

Formula:

Please Login or Register  to view this content.

v	A	B	C	D	E
1	Category	Type	Worker	Lookup Value	Location
2	Red	Burning	David carried 27 atl002's with him each day.	atl002	C2
3	Red	Burning	Steve carried 28 atl003's with him each day.
4	Red	Burning	Bill carried 47 atl005's with him each day.
5	Red	Burning	Lee carried 7 atl002's with him each day.		C5
6	Red	Burning	John carried 67 atl012's with him each day.
7	Red	Burning	Justin carried 57 atl002's with him each day.		C7
8	Red	Burning	Mike carried 31 atl015's with him each day.
9	Red	Burning	Eric carried 18 atl016's with him each day.
10	Red	Burning	Dave carried 25 atl002's with him each day.		C10
11	Blue	Cooling	Randall carried 27 atl020's with him each day.
12	Blue	Cooling	Wayne carried 28 atl003's with him each day.
13	Blue	Cooling	Rick carried 47 atl005's with him each day.
14	Blue	Cooling	Ben carried 27 atl010's with him each day.
15	Blue	Cooling	Travis carried 6 atl002's with him each day.		C15
16	Blue	Cooling	Mark carried 12 atl014's with him each day.
17	Blue	Cooling	Thomas carried 15 atl002's with him each day.		C17
18	Blue	Cooling	Johnny carried 18 atl002's with him each day.		C18
19	Blue	Cooling	Harry carried 27 atl019's with him each day.

Data Range

	D	E
1	Lookup Value	Location
2	atl002	C2
3		C5
4		C7
5		C10
6		C15
7		C17
8		C18
9

This array formula** entered in E2:

=IFERROR(ADDRESS(SMALL(IF(ISNUMBER(SEARCH(D$2,C$2:C$19)),ROW(C$2:C$19)),ROWS(E$2:E2)),3,4),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

You're welcome. Thanks for the feedback!