Return last date something was entered into one column with criteria from another column

Question

I'm working with a set of data in excel. Data is entered into rows for items specified in columns. The first column contains a date. A cell in the same row of one of the columns contains the name of a person and in another cell in the same row but different column may contain a number larger than zero (or it may be empty). I need to create a formula which returns the date when a number larger than zero was last entered into that column for a specific name. This is a "living list" which keeps on growing and the same names appear in different rows, sometimes with a number in the column a mentioned and sometimes not. I found an old thread on this site on a similar subject which got me as far as knowing the date of the last entry containing the persons name but I'm still not able to configure it to show me when that specific person also had a number larger than zero in that column. Here's the thread: How to get the newest value from a column with conditions

My current formula looks like this:

=INDEX($A:$A,MATCH(MAX(IF($G:$G=Sheet7!C5,$A:$A,0)),IF($G:$G=Sheet7!C5,$A:$A,""))) CTRL+SHIFT+ENTER

Column A contains the dates Column G contains the names (and "Sheet7!C5" is a reference to a name) The value column I need to add to the mix is column AY

I feel there must be a simple solution (a small add on to the formula) to solve this but I always end up with an error.

Thanks in advance :)

Edit: Here is a simplified example of the data entry and output list needed.

Answer 1

For this you need to sort the date DESCENDING and format the table as Excel Table

Edit: you can sort the date ascending. See explanation at the end.

Using your example, then the formula will be

=INDEX(TableData[Activity A], MATCH($B14, TableData[Employee initials], 0))

This works just like the usual VLOOKUP or INDEX MATCH, fetching the first date on an activity matching the employee initials.

You can use VLOOKUP, but you'll need to dynamically name the range of each columns.

Edit: Just today I found an interesting behavior of MATCH when it found multiple matching values. If you use 1 instead of 0, then it will fetch the last matching value on the list.

So, you can use this formula instead in ASCENDING table.

=INDEX(TableData[Activity A], MATCH($B14, TableData[Employee initials], 1))