Forum Discussion

IEC177's avatar
IEC177
Copper Contributor
Oct 20, 2021
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...
excel
Formulas and Functions
  • Juliano-Petrukio's avatar
    Juliano-Petrukio
    Oct 20, 2021

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

     

Juliano-Petrukio's avatar
Juliano-Petrukio
Bronze Contributor
Oct 20, 2021

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

 

IEC177's avatar
IEC177
Copper Contributor
Oct 28, 2021
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.

Resources