I have a complicated master inventory spreadsheet from which I need to
generate shorter reports for items currently in use. My column headings are
as below:

A Product
B Color
C Volume Used
D Manufacturer
E Catalog Number
F Lot number
G Date received
H Expiration Date

However, we may have a given product in several different colors.

All 8 columns need to show up in my report.

I would like to be able to use VLOOKUP (or something else that works) to
find (for example) Product X in green and then be able to drag the formula
to return the rest of the 7 columns as well. If I concatenate first, I can't
drag. If I just link the cells then the report gets jumbled up every time
somebody sorts the inventory sheet. If I have to type in a separate vlookup
formula in each column, I'll still be typing this time next year. I know
this really should be done in Access, but unfortunately, that's not an option.

Any help would be greatly appreciated.

Thanks,
JenL