Aug 22 2020 03:05 PM
Aug 22 2020 03:57 PM
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))
Aug 23 2020 09:16 AM
Thanks for your response. However, the formula is not working, it gives me an error when entered.
Aug 23 2020 09:55 AM
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 )
Aug 23 2020 11:02 AM
Aug 24 2020 08:59 AM