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,

would something like this do it for you ?

=MAX(XLOOKUP(A3,Training!A$4:A$100,Training!G4:O100,"NR"))

(also, good to update your profile here, so we can see which version of Excel you are using)

cheers
Rob

 

[eobrien]

Thank you. I am using Microsoft 365 Apps for Business. I updated. This does work, but is there away to get a result for anything that has no date in it yet...to result in "Schedule"? So, if blank than kick out or give a result of "Schedule"?

 

[eobrien]

Thank you. I am using Microsoft 365 Apps for Business. I updated. This does work, but is there away to get a result for anything that has no date in it yet...to result in "Schedule"? So, if blank than kick out or give a result of "Schedule"?

 

[eobrien]

Can anyone assist with this? =MAX(XLOOKUP(A3,Training!A$4:A$100,Training!G4:O100,"NR"))

This does work, but is there away to get a result for anything that has no date in it yet...to result in "Schedule"? So, if blank than kick out or give a result of "Schedule"?

 

[Cubist]

Maybe this

=IFERROR(MAX(XLOOKUP(A3,Training!A$4:A$100,Training!G4:O100)),"Schedule")

 

[Cubist]

Or this...

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

 

[eobrien]

Maybe this

=IFERROR(MAX(XLOOKUP(A3,Training!A$4:A$100,Training!G4:O100)),"Schedule")

This one worked! Your my hero! Thank you! E

 

[RobP]

Thanks for feedback, glad we could help.

Rob

 

[eobrien]

I spoke to soon. It partially works, but not completely. The 2nd suggestion did not work at all, the 1st suggestion works a little. It isn't recognizing Jothirajah or Boles or Ames, who has no dates scheduled and giving the answer of "schedule". It is giving the correct answer for Chen. The people that is saying "Schedule" for aren't on the training list. It should only result in schedule, if they are on the training summary with no dates scheduled. Is there a fix for this or am I just using the wrong type of formula? Any help is appreciated. E.