Return Latest Date from List of Various Activities

[aragno]

1) I have a list of 10 activities which are periodically performed at different intervals. (we will be adding more activities)

2) I have a list of every time an activity is executed which records what activity and the date of execution. This is the "activity register"

3) I created a distinct unique list of values from the "activity register"

The real question: I want to populate the "last executed date" from the "activity register" into the "last executed" column, keeping in mind the "activity register" will grow and the number of unique activities will also grow.

I tried messing with arrays but couldn't find what I needed.

See attached spreadsheet which has some sample data.

[Ace_XL]

If your dates are in chronological order use in Cell C3
=LOOKUP(2,1/($F$21:$F$33=A3),($G$21:$G$33))

Even if they are not in chronological order, this will work
=MAX(INDEX($G$21:$G$33*($F$21:$F$33=A3),0))

You may replace the reference in Column F with a named range to accommodate more/less entries

[modytrane]

This works:

=MAX((A3=F$21:F$33)*(G$21:G$33))

As an array formula.
Format the result as Date.

[aragno]

If your dates are in chronological order use in Cell C3
=LOOKUP(2,1/($F$21:$F$33=A3),($G$21:$G$33))

Even if they are not in chronological order, this will work
=MAX(INDEX($G$21:$G$33*($F$21:$F$33=A3),0))

You may replace the reference in Column F with a named range to accommodate more/less entries

Hi Ace_XL,

This worked well and I've gone further to improve my file with dynamic named ranges (using offset), organized into various sheets, etc.

However, when I try to use the =MAX(INDEX(.....) formula with my named range I am getting "N/A"

I have attached another file (activity_master_record_20191210.xlsx)and you can see the error on sheet "overview", cell "C2"

Any idea what I have screwed up?

[modytrane]

change your named range definition to:

=OFFSET('activity register'!$B$2,0,0,COUNTA('activity register'!$B:$B)-2,1)

It works.

[modytrane]

make sure number of items in your named range matches with number f items in ('activity register'!$A$2:$A$14=A2).

[modytrane]

Better yet. Use following for your named ranges and use the formula below.

data_activity_register =OFFSET('activity register'!$A$2,0,0,COUNTA('activity register'!$A:$A)-1,1)
data_executed_date =OFFSET('activity register'!$B$2,0,0,COUNTA('activity register'!$B:$B)-1,1)

[Ace_XL]

Hi Ace_XL,

This worked well and I've gone further to improve my file with dynamic named ranges (using offset), organized into various sheets, etc.

However, when I try to use the =MAX(INDEX(.....) formula with my named range I am getting "N/A"

I have attached another file (activity_master_record_20191210.xlsx)and you can see the error on sheet "overview", cell "C2"

Any idea what I have screwed up?

Your ranges are inconsistent. The named range is Rows 2 to 16, so your other range should also be the same i.e.

In Cell C2
=MAX(INDEX(data_executed_date*('activity register'!$A$2:$A$16=A2),0))