CODEWITHSUNDEEP
exceldateindexingexcel-formulaexcel-2010
johnnywin
johnnywin

Reputation: 11

Formula to report back text field based on multiple criteria and date range

I'm trying to create a formula that searches a separate sheet and reports back the correct cell with a text string. The criteria (if filtering the sheet) contains a date range, <=DATE(YEAR(U$1),MONTH(U$1)+1,DAY(U$1))-31 essentially anything older than 30 days from the end of the month, where the date is set by the user in cell U$1 and matching a text string. I have tried several iterations of INDEX MATCH, using MATCH in an array but keep getting the #N/A error.

The logic is to return the contents of the appropriate cell in column G, if the date is older than 30 days before the end of the month (date in column F), sorting by oldest to newest and column P must contain the text string "file not validated"

=COUNTIFS(Summary!P:P,"file not validated",Summary!F:F,"<="&DATE(YEAR(U$1),MONTH(U$1)+1,DAY(U$1))-31)

This formula returns the correct number of entries that match the criteria, however, I am unable to insert index match to display the oldest entry

Upvotes: 1

Views: 143

Answers (1)

user4039065
user4039065

Reputation:

Use INDEX/AGGREGATE as a standard formula; AGGREGATE supplies the array processing without CSE the same way SUMPRODUCT does. Avoid full column references for this reason.

=iferror(index('Summary'!G:G,
  aggregate(15, 6, row(Summary!P$1:index(Summary!P:P, match("zzz", Summary!P:P)))/
 ((Summary!P$1:index(Summary!P:P, match("zzz", Summary!P:P))="file not validated")*
  (Summary!f$1:index(Summary!f:f, match("zzz", Summary!P:P))<=DATE(YEAR(U$1),MONTH(U$1)+1,DAY(U$1))-31))), 1)), "")

You stated in your narrative that your end-of-month was working correctly but it doesn't look like it should. I've offered a couple better formulas for calculation the end of month in comments. You might want to look into that.

Upvotes: 1

Related Questions