look up and display last max value in a column

p45cal · #9 01-10-2024, 08:38 AM

Looks like you're still on Excel 2013; can you confirm?

If so, then this is not straightforward. I've put together one solution, but it's not great. If I think of a better solution I'll post again.
So, in cell E5:

Code:

=INDEX(B5:B1000,MATCH(MAX(IF(B5:B1000>INDEX(B5:B1000,MAX((B5:B1000<>"")*(ROW(B5:B1000)))-ROW(B5)+1),ROW(B5:B1000))),IF(B5:B1000>INDEX(B5:B1000,MAX((B5:B1000<>"")*(ROW(B5:B1000)))-ROW(B5)+1),ROW(B5:B1000)),0))

which should be entered onto the sheet using Ctrl+Shift+Enter, not just plain Enter (called array-entering).

A slight variant, array-entered into cell G5 to give the date:

Code:

=INDEX(A5:A1000,MATCH(MAX(IF(B5:B1000>INDEX(B5:B1000,MAX((B5:B1000<>"")*(ROW(B5:B1000)))-ROW(B5)+1),ROW(B5:B1000))),IF($B$5:$B1000>INDEX(B5:B1000,MAX((B5:B1000<>"")*(ROW(B5:B1000)))-ROW(B5)+1),ROW(B5:B1000)),0))

Note that as @ArviLaanements pointed out, this one uses previous row, not previous date.

Also, it currently looks only at rows 5 to 1000, you need to adjust it if you need more rows (or fewer too, perhaps).
Note that it depends on there being all empty cells in that range in column B below the last score.

If you're on Office 365 the formula can be much shorter.

See attached.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Display of predecessor and successor column	tropaj	Project	3	04-03-2022 04:12 PM
display a change in column cells counting the number of months	oscarlimerick	Excel	4	03-14-2022 12:22 AM
Display an additional date column in Gantt chart	ds1	Project	4	04-17-2017 01:34 PM
Embedded Excel in Powerpoint - custom animation, display column by column	andytheanimal	PowerPoint	2	01-20-2015 06:30 AM
Excel Fomula to search for a string and display value from different column	zeeshanbutt	Excel	1	07-29-2012 12:48 AM