Formula to pull latest value used from series of columns 1

[Scott02]

Good afternoon all,

I was approached with an excel question and got it to work using nested if statments, but if there had been one additional column, that option would not have worked. The request was to return the latest item price used over a series of months (Jan - Sept.) Prices skipped months and went up and down, and the largest was not always the last price used. Does anyone know of a good formula to use to do this next month that won't exceed the max nested function limit of 7? Here's an example of the table
1 2 3 4 5
A LIST JAN FEB MAR APR
B Item A 1.90 0 1.89 0
C Item B 0 0 0 0
D Item C 5.15 0 0 0
E Item D 2.75 3.14 3.00 2.95


Thanks for your time.

Scott

 

[Zathras]

You can use an array formula: Enter this in cell K2:
[tt]
=INDEX(A2:J2,1,MAX(IF(B2:J2=0,0,COLUMN(B2:J2))))
[/tt]
and put in the worksheet with Ctrl+Shift+Enter. When done correctly, you should see curly braces around the formula in the formula bar. And you should see the last price used in K2.
Copy down as far as needed for your items.

I'm leaving now, so if you post back I won't see it for several hours. If you have any questions, look up "array formula" in the help file.