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

Question

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 ?

Answer 1

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.

Answer 2

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.