Find the month of the last non-zero value in a row

[pliskers]

I have a database with a header row containing month-year periods, and rows of data showing sales within each period. The dates move from earliest to latest as you go from left to right.

On each row, I need a formula to the right of column Z (assume the data fills columns A-Z) that will deliver the value in the date header row that corresponds to the last non-zero (>0) value within that row, between columns A and Z.

Any help will be much appreciated!

 

[VoG]

For row 2 try

=INDEX(1:1,LOOKUP(9.9E+307,2:2))

 

[pliskers]

Sorry, it's just returning a zero. The formula needs to read the name of the month in row 1, and the formula needs to appear to the right of the last column of data in each row. That would put the formulas in column AA, and each should look at the values in that row up through column Z, and find the month in which the rightmost nonzero value occurs in that row.

 

[VoG]

Are the values numbers or text?

 

[pliskers]

All values, some of which repeat themselves within a row

 

[Aladin Akyurek]

Sorry, it's just returning a zero. The formula needs to read the name of the month in row 1, and the formula needs to appear to the right of the last column of data in each row. That would put the formulas in column AA, and each should look at the values in that row up through column Z, and find the month in which the rightmost nonzero value occurs in that row.

AA2, copy down:

=LOOKUP(9.99999999999999E+307,A2:Z2,$A$1:$Z$1)

 

[Haseeb Avarakkan]

Try, AA2 copy down...

=LOOKUP(2,1/(A2:Z2),A$1:Z$1)

 

[pliskers]

Works perfectly, thanks so much!

 

[pliskers]

How does the 9.99999999999999E+307 work?

 

[pliskers]

Uhoh, I just found a glitch. If a zero is in the rightmost column, it's evaluating it as a value. Only if I blank out the cell does it defer to the next cell with a value in it moving left. A zero should be evaluated the same way as if the cell were blank - the month needs to show the month with the last non zero value.