Google Spreadsheet Formula to get last value in a row

Question

I wondered if someone could help me with what I hope is a simple Formula.

I have a simple spreadsheet for Product which has a column for the product name eg

Product A

Product B

Product C

Product D

Now on each row there are some numerical values

eg

Product

Product A 5.0 2.5

Product B 6.0

Product C 2.0 4.0 5.0

Product D 3.0

Product E 6.0 2.0 1.6 2.9

Now what I want is to have a formula for a Column next to the prouct that shows me the value of the last entered value for a Product eg the Values in the above example would give me

Product A 2.5

Product B 6.0

Product C 5.0

Product D 3.0

Product E 2.9

In Excel I would do this with INDEX and MATCH however I cannot get this to work in Google Spreadsheets.

Any ideas would be most welcome.

Here is a screenshot of what I would like it to look like. http://i.imgur.com/jqcNW.png

Many thanks in advance

Answer 1

For me this one works better, mainly because it works with any other formulas in the same row:

=LOOKUP(9999999999; (B2:G2))

Answer 2

I managed to do it :)

Formula

=index(B2:G2;1;counta(B2:G2))

Answer 3

Will update @mik's answer once I have a high enough reputation.

As a more general answer than @mik's, you can have a formula that slides with the placement of the data:

=index(B2:G2, 0, max(ARRAYFORMULA(column(B2:G2)*(B2:G2<>""))) - column(B2) + 1)

Answer 4

I used a similar solution to @DannyhelMont, but I adapted it to work with strings. I had to fill the first column in the range with values to keep from getting an error.

The string of z's is intended to appear alphabetically later than every other possible string. The hlookup function returns the last value which is less than or equal to the search value. (If you're doing numbers, use 9999999999 instead of the z's.)

=HLOOKUP("zzzzzzzzzz",B2:G2,1,true)

This has an advantage over the index/counta solution given by @DarkUFO because it doesn't depend on the number of cells with values. If any cell in the range is empty, counta returns a number less than the offset of the last cell. The hlookup solution can have empty cells. If all cells are empty it gives an error.