heretolearnexcel
Board Regular
- Joined
- Jan 22, 2019
- Messages
- 58
- Office Version
- 365
- Platform
- Windows
Here's the formula I'm trying to use, but I keep getting an array filled with #VALUE errors:
=LET(Return;XLOOKUP(MAX(data!N2:N1170);(data!AG2:AG1170=J2)*data!N2:N1170;data!N2:N1170;"not found";-1);Range;(data!AG2:AG1170=J2)*data!N2:N1170;IF(NOT(NOT(COUNTIF(Range;"<>0")));Return;""))
I need the formula to return the max date that corresponds to the criteria J2. If the criteria J2 isn't found, then I want to get a blank result: ""
Column N corresponds to date values, and column AG is where the criteria J2 will be looked up.
If I copy and paste each of the names I defined in the formula, "Return" and "Range", to a different cell I get a result that isn't an error, so the problem is with the IF formula.
Is there a way to change my formula so that it works, or is there an alternate solution?
Thank you.
=LET(Return;XLOOKUP(MAX(data!N2:N1170);(data!AG2:AG1170=J2)*data!N2:N1170;data!N2:N1170;"not found";-1);Range;(data!AG2:AG1170=J2)*data!N2:N1170;IF(NOT(NOT(COUNTIF(Range;"<>0")));Return;""))
I need the formula to return the max date that corresponds to the criteria J2. If the criteria J2 isn't found, then I want to get a blank result: ""
Column N corresponds to date values, and column AG is where the criteria J2 will be looked up.
If I copy and paste each of the names I defined in the formula, "Return" and "Range", to a different cell I get a result that isn't an error, so the problem is with the IF formula.
Is there a way to change my formula so that it works, or is there an alternate solution?
Thank you.