Forum Discussion
Returning the last value of a row to another cell
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
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 )- Thanks Peter.
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))
Thanks for your response. However, the formula is not working, it gives me an error when entered.
- Replace semi-colon(;) by comma (,)
OFFSET($F$7,1,MATCH(MAX(G7:P7),G7:P7,0))
