Find Highest (my recent ) date where the adjacent cell doesn't = "No Data"

[APML]

Hi All, I'm looking for a formula to : (Date Range is C10 : C28 and Close Price is D10 : D28)
Return the highest date where the adjacent cell doesn't equal "No Data"
Note the Date Range won't necessarily be in date order as shown in my screenshot
All help is appreciated.

 

[Joe4]

Try this formula:

=MAXIFS(C10:C28,D10:D28,"<>"&"No Data")

 

[APML]

Thx works great. I'm using it to return the adjacent value which is used in another formula. The below works (using your formula), just wondering if there's an easier way?
=XLOOKUP(MAXIFS(C10:C269,D10:D269,"<>"&"No Data"),C10:C269,D10:D269,"No Data",0)

 

[Joe4]

Thx works great. I'm using it to return the adjacent value which is used in another formula. The below works (using your formula), just wondering if there's an easier way?
=XLOOKUP(MAXIFS(C10:C269,D10:D269,"<>"&"No Data"),C10:C269,D10:D269,"No Data",0)

You could probably use XLOOKUP, VLOOKUP, or INDEX/MATCH.
They should all work.
I hear that INDEX/MATCH might be a little more efficient, but XLOOKUP and VLOOKUP are a little more intuitive, IMO.

 

[APML]

Thx for your help, I really appreciate it.

 

[Joe4]

You are welcome.

 

[Peter_SSs]

just wondering if there's an easier way?

Your data appears to be chronological (from bottom up) so it seems that you are just looking for the first number in column D. If that is the case then there is no need to even calculate the date.

=INDEX(FILTER(D10:D269,ISNUMBER(D10:D269)),1)

 

[Joe4]

Your data appears to be chronological (from bottom up) so it seems that you are just looking for the first number in column D. If that is the case then there is no need to even calculate the date.

Based on what they said in the original question, I don't think we can assume that will always be the case.

Note the Date Range won't necessarily be in date order as shown in my screenshot

 

[Peter_SSs]

Based on what they said in the original question, I don't think we can assume that will always be the case.

Thanks Joe - should have put my on!

=INDEX(SORT(FILTER(C10:D269,ISNUMBER(D10:D269)),1,-1),1,2)