Google Spreadsheet Formula to get last value in a row

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



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.

Many thanks in advance


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

=LOOKUP(9999999999; (B2:G2))

I managed to do it :)



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)

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.)


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.

If you can have both numbers and strings, or do not know a value that is greater than any possible number or string, you can do:


Need Your Help

Template.autorun() and Tracker.afterFlush() not working

meteor meteor-blaze autorun

From what I could understand from the official docs, function passed as parameter to template.autorun should run whenever there is an update in the DOM.