# Excel/Spreadsheet - formula for last value in a row?

I'm trying to forecast increasing vendor costs in a spreadsheet.

My sheet looks something like this

col     | A         | B         | C         | D         | E         | F
vendor  | 2013-09   | 2013-10   | 2013-11   | 2013-12   | 2014-01   | 2014-02
inet    | 50        | 50        | 50        | 60        | 60        | 60
scm (yr)| 300       |                                   | 400       |
- (mn)| =(300/12) | =(300/12) | =(300/12) | =(300/12) | =(400/12) | =(400/12)

The 'scm' column shows the Source Control vendor's yearly cost (yr) and then monthly cost, which references the yearly cost's column and then divides it by 12. [ ie , "(\$A,\$scm(yr))/12"

The annoying part is when I have a new cost come in -- as in Janu 2014 -- and I have to update the formulas.

Is there a command that would let me reference "the last value in row X , before or equal to the current column"? this way I could use a single formula for an entire row ( like the monthly 'scm' costs ), and just shift numbers around in the yearly row , without having to recode formulas. For example, in relation to the 'scm' row, the value in cols A,B,C,D would be 300 ; while the value in cols E,F would be 400 ?

-

You can use LOOKUP() as well:

=LOOKUP(9.99E207, \$B\$3:B\$3)
^----- Column formula is inputted
^--^------- Starting cell

In cell B4 and drag to the right.

-
Selected because this is simpler. Both EXCELLENT answers that have ended years of wonder! –  Jonathan Vanasco Jul 9 '13 at 14:23

Starting in column B, any row,

=INDEX(3:3,MATCH(99^99,\$A\$3:B\$3,1))

copy across. Integrate into a formula as required.

See screenshot. Note that the sample file for the screenshot has the labels in column A, so the values start in column B. Adjust as needed.

-
thank you! i've wanted to know how to do this forever! –  Jonathan Vanasco Jul 9 '13 at 14:24