Reputation: 692

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

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. https://i.sstatic.net/Qa8Ez.png

Many thanks in advance

Upvotes: 13

Answers (6)

Blairg23

Reputation: 12045

This will return the value of the 1st column and last row of a range named RangeName:

=INDEX(RangeName, ROWS(RangeName), 1)

where RangeName is the name or range you are looking at. ROWS returns the number of rows in that range.

Upvotes: 0

Jeff the Demographic Economist

Reputation: 103

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)

Upvotes: 4

mik

Reputation: 3875

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:

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

Upvotes: 2

Mnebuerquo

Reputation: 5949

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.

Upvotes: 3