Hello. I am having issues trying to meet a request. The request is that for a very large report, I need to produce the MAX (or most recent) date in column S for each name in column D, so long as the value in column I = "Annual Update (B)"

I tried a pivot table to return max date as values with the Report Filter = "Annual Update (B)" only, but I only get values of 0. Then I tried this, with all the unique names on a separate worksheet in column B and this formula in column C next to each name and it fails. Please help!

=IF(AND(VLOOKUP("Annual Update (B)",CADData!$D$2:$S$26894,6,FALSE)),(VLOOKUP($B3,CADData!$D$2:$S$26894,6,FALSE),MAX(VLOOKUP($B3,CADData!$D$2:$S$26894,16,FALSE),""))