Iferror(vlookup) with result being individual's max date for training

[eobrien]

Good Morning All,

I need a formula to place in Contract Sheet (Column E) that will reference a specific person's name from the training sheet (Training A4:A100 (Name) and then give a result of the maximum date in range (Training G4:O100 Floor Training)the range. Its a worksheet to worksheet pull. I tried on the contract sheet in column E {=max(if(Training!$A$4:$a$100=$a3,training!$g$4:$g$100))}. It doesn't work. Any help would be greatly appreciated! E

 

[RobP]

Hi, Try to modify it like this :

=LET(m,MAX(XLOOKUP(A3,Training!A$4:A$100,Training!G$4:O$100)),IF(m=0,"Schedule",m))

This formula is to be inserted on Row 3 of your Contract sheet - as the XLOOKUP(A3 portion is for row 3. As you copy it down, it should move to A4, A5 etc. but keep the dataset between rows 4 and 100.

The G$4:O$100 needs to have the $ sign to ensure it keeps track of your whole data set (assuming the training sheet runs from row 4 to row 100 as you copy the formula down your sheet.... otherwise you will see it move to 5-101, 6-102, 7-103 etc inside the formula. but you can modify it accordingly to your last row of data if you have 200 rows for example...

Rob

 

[Alex Blakenburg]

Slight modification to the above to cover the not found scenario

=LET(maxDate,MAX(XLOOKUP($A3,Training!$A$4:$A$100,Training!$G$4:$O$100,"")),IFERROR(IF(maxDate=0,"Schedule",maxDate),""))

And the filter alternative

=LET(maxDate,MAX(FILTER(Training!$G$4:$O$100,Training!$A$4:$A$100=$A3,"")),IFERROR(IF(maxDate=0,"Schedule",maxDate),""))

 

[eobrien]

Slight modification to the above to cover the not found scenario

=LET(maxDate,MAX(XLOOKUP($A3,Training!$A$4:$A$100,Training!$G$4:$O$100,"")),IFERROR(IF(maxDate=0,"Schedule",maxDate),""))

And the filter alternative

=LET(maxDate,MAX(FILTER(Training!$G$4:$O$100,Training!$A$4:$A$100=$A3,"")),IFERROR(IF(maxDate=0,"Schedule",maxDate),""))

Thank you Alex!

 

[Alex Blakenburg]

You're welcome. Glad we could help.