Forum Discussion
Solved
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 th...
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)))
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)))
- 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. - 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.
