SOLVED

New Contributor

# Inserting a max function into an Index Match

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.

3 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

# Re: Inserting a max function into an Index Match

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)))``

# Re: Inserting a max function into an Index Match

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.

# Re: Inserting a max function into an Index Match

Hi Juliano,
Just to let you know that my problem is resolved. It was a 'MaxIf' that did it. The formula look like this: