Return a value in a reference row based on the last occurence of a value in an array

[zestygirl]

Ok. This might be better illustrated than explained....

I have a header row (row A) numbered as such (they are week numbers from 2010/2011):
B1: 51
C1: 52
D1: 1
E1: 2

Then, I have part numbers in the first column like such:
A2: 101000
A3: 201000
A4: 301000
A5: 401000

Then, I have amounts sold next to each item for the corresponding week# Like such:

B2: (corresponds to sales for 101000) 0 (wk51) C2: 0 (wk52) D2: 1 (wk1) E2: 1 (wk2)
B3:5 C3:7 D3:6 E3:0
B4:0 C4:0 D4:0 E4:0
B5:1 C5:1 D5:1 E5:1

I wish to write a function to return the following result:

FOR EACH PART: LOCATE THE LAST CELL IN THE ROW CONTAINING A VALUE >0 AND RETURN THE REFERENCE VALUE FROM THE HEADER ROW (ROW A, WEEK #)

This tells me when was the last time we sold this item. This grid is massive with 80,000 records and 26 weeks.

The result if I wrote the correct formula (struggling) would be as follows: (if I were to put the results in column F for each item)

F1: "Last movement wk#"
F2: 2 (last cell containing a value >0 is E2 (contains "1") so would return value 2 (week number, from cell E1).
F3: 1
F4: 0 (none sold, so zero returned or error value such as N/A#)
F5: 2

Any help would be greatly appreciated. I am 'familiar' with functions such as array formulae (in braces), offset, max, match, and lookup. I cannot, however, seem to put anything together to get me the right answer!

Thanks sooooo much. This is for work, due tomorrow, and I am out of ideas aside from pouring the whole thing into access.

[NBVC]

IN F2:

=LOOKUP(2,1/($B2:$E2>0),$B$1:$E$1)

copied down

[zestygirl]

Excellent! I had written something similar, but had failed to make the ranges absolute.

Thanks so much!

And from Mississauga. A little help from my friend (I'm in sauga too).