I use Excel 2007, but will need to share this workbook with 2003 users.

On worksheet 1, I want to pick from a list of possible options for info that
goes into column for Subs from the row that matches the Mfg on worksheet 2.

Not all Mfg may have a Sub option, therefore I may want to repeat the Mfg
name.
Both worksheets will have adds and deletes over the course of the year.

Worksheet 1

Parts Mfg Sub
123 Stars ??
234 Color ??
345 Cars ??
888 Color ??
777 Color ??
557 Cars ??
735 States ??

Worksheet 2
Mfg Sub
Stars Venus Mars Saturn Jupiter
Color Blue Black Red Green Yellow
Cars Ford GM Toyato
States

I tried the vlookup statement, but it only returns the value from the column
that I define in the formula in worksheet 1 C2

=VLOOKUP(B2,'Worksheet 2'!A$2:H1066,2)

Answer would be Venus, but I would like for it to be Jupiter.

Because worksheet 2 is so large, it would be hard to create a name range for
each row.

Any suggestions?
Thanks so much