Hi

I work in a promotional department for a retailer and I use a large document that I call the "promotional planner" to plan when I put products on promotion, according to "Promo Weeks".

Every product has an item number. I want to find out the last promo week an item went on sale by typing in the item number in a certain column. Basically the spreadsheet appears thus:

Column A: Promo Week
Column C: Item Number
Column D: Item Description
Column E: Previous Promo Week
(missing columns contain other info)

At the moment we are manually searching through the sheet to search for the previous promo week (we need at least 10 weeks between each promotion). Ive tried VLOOKUP and match, but they don't seem to seach backwards. I know the logic is that column E should search column C, and then return the result of column A, but it needs to work "backwards", or "upwards", through the sheet so I definitely have the last promo week.

To make matters even more confusing, if the font colour of the previous promo week is red, I want it to ignore it and search further back, as red is what we use to indicate when we cancelled the promotions (we keep it in the spreadsheet for reference).

I also have the issue where 2011 promotions are in one workbook, and 2010 are in another. Could I edit a formula to search the 2010 planner too, if the 2011 provides no results, to get the previous promo week in 2010, provided I knew the exact file path? (which never changes).

Thanks in advance for any help you can provide.