Returning the last value of a row to another cell

Occasional Contributor
Hi team,

Could someone help with a formula to return values from a range of cells to a particular cell depending on dates. For example, the last cell value of that row is always copied to another cell as the date changes.

In the attached example, I want cell S7 to be populated with the most recent value on row 8 depending on the latest date.

Thanks.
6 Replies

@Dazumi30 

There are several ways to get it.

The general idea is to get the latest date. You can get it by using the MAX() formula for example.

Once you know the most recent date, what you need to to is find the "position" of this value. This you can get by using the MATCH() formula. =MATCH(MAX(G7:P7);G7:P7;0). It will give you the position of the value.

Once you know the position you can use a formula like OFFSET() to displace up to the value, I mean, returning the value of tha position.

=OFFSET($F$7;1;MATCH(MAX(G7:P7);G7:P7;0))

@Juliano-Petrukio 

 

Thanks for your response. However, the formula is not working, it gives me an error when entered.

@Dazumi30 

The question is somewhat ambiguous.  Firstly, the dates came over Jan 1 to Jan 10.  Is that what was intended or should it be read as the 1st of each month from Jan-Oct?

What is the 'latest' date?  Is it 1st Aug since we are now in August, or the 1st Oct since that is the last date in the table?

 

A possible answer (assuming the labels 'date' and 'value' are applied to the series as names

= LOOKUP( TODAY(), Date, Values )

or with modern Excel

= XLOOKUP( TODAY(), Date, Values, , -1 )

 

Replace semi-colon(;) by comma (,)
OFFSET($F$7,1,MATCH(MAX(G7:P7),G7:P7,0))
Thanks Peter.