Inserting a max function into an Index Match

[IEC177]

Hi,

I am trying to maintain a training matrix for a number of staff, who have a number of refresher courses to do. I have a table for compiling the training course title against their URN with the date of the training, which I have a power automate linked to a form.

I then have the matrix pulling in the date of the training from the table by URN and Title. However, it only pulls in the first date and isn't finding the date when they have refreshed the course.

The formula I am using, using First Aid at Work as a course example, is:

=INDEX(AllData[Date],MATCH("[the users urn]",AllData[URN],0)*MATCH("First Aid at Work (valid 3 years)",AllData[Title/Subject],0))

I'm thinking it will be by way of adding a MAX function somewhere but everything I've tried so far still only pulls in the first date or throws up an error.

Has anyone any suggestions please?

[#2 · Accepted solution]

Substitute the literal names for the proper range of your data.
You have some different ways to achieve it

=MAX(INDEX(((URN=URNValues)*(TrainingName=TrainingValues))*DateValues,))

Using MAXIFS()

=MAXIFS(DateValues;URNValues,URN,TrainingValues,TrainingName)

Using XLOOKUP

=XLOOKUP(MAX(DateValues);(URNValues=URN)*(TrainingValues=TrainingName)*DateValues;DateValues;;-1)

Using FILTER() formula

=MAX(FILTER(YourTable, (URNValues=URN)*(TrainingValues=TrainingName)))

[#3]

Hi Juliano, Many thanks. I tried one or two but it still didn't give me what I hoped for. I'll try the other and see if they work. Thanks again.

[#4]

Hi Juliano,
Just to let you know that my problem is resolved. It was a 'MaxIf' that did it. The formula look like this:
{=MAX(IF(FAData2[URN]=B$2,IF(FAData2[Title/Subject]=[@Title],FAData2[Date])))}
(Row B contains the URN for each person, whose training dates run in columns)

Many thanks for your assistance.