MAXIFS with ARRAYFORMULA not working in Google Sheets

2
votes

Please help wonderful people!

I'm trying to use a MAXIFS formula within an ARRAYFORMULA in Google Sheets to obtain the latest date in column A from sheet 1, where the value in column A of sheet 2 matches in column E in sheet 1. Here's my formula:

=ARRAYFORMULA(IF(ISBLANK(A2:A),"",IF(MAXIFS(Sheet1!$A:$A,Sheet1!$E:$E,A2:A)=0,"",MAXIFS(Sheet1!$A:$A,Sheet1!$E:$E,A2:A))))

Sheet 2:

Screen shot of the formula in sheet 2

Sheet 1:

Screen shot of the lookup data in sheet 1

I then repeat these in the 6 further columns to give the previous 6 dates using an extra criteria in the MAXIFS formula where I ask for the latest date, less than the previous column. Formula as follows:

=ARRAYFORMULA(IF(ISBLANK(A2:A),"",IF(MAXIFS(Sheet1!A:A,Sheet1!E:E,A2:A,Sheet1!A:A,"<"&B2:B)=0,"",MAXIFS(Sheet1!A:A,Sheet1!E:E,A2:A,Sheet1!A:A,"<"&B2:B))))

Screen shot of sheet 2 and the above formula setup

It all works on the first row but every row after that gives the same results suggesting the formula must be using the value in A2 as the criterion in the MAXIFS statement on all subsequent rows.

I can't see what I've got wrong so it makes me think MAXIFS in ARRAYFORMULA may not work...

Please help

1
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasmaxifs
Here's a link to a copy of the file... – Charlie Merritt
docs.google.com/spreadsheets/d/… – Charlie Merritt

1 Answers

0
votes

paste in B2:

=ARRAYFORMULA(TO_DATE(IFNA(VLOOKUP(A2:A, 
 SORTN(SORT({Sheet1!E2:E, Sheet1!A2:A}, 2, 0), 999^99, 2, 1, 1), 2, 0))))

0

paste in C2:

=ARRAYFORMULA(TO_DATE(IFNA(VLOOKUP(A2:A, 
 SORTN(SORT(FILTER({Sheet1!E2:E, Sheet1!A2:A}, 
 NOT(COUNTIF(A2:A&B2:B, Sheet1!E2:E&Sheet1!A2:A))), 2, 0),
 999^99, 2, 1, 1), 2, 0))))

paste in D2:

=ARRAYFORMULA(TO_DATE(IFNA(VLOOKUP(A2:A, 
 SORTN(SORT(FILTER({Sheet1!E2:E, Sheet1!A2:A}, 
 NOT(COUNTIF({A2:A&B2:B; A2:A&C2:C}, Sheet1!E2:E&Sheet1!A2:A))), 2, 0),
 999^99, 2, 1, 1), 2, 0))))

paste in E2:

=ARRAYFORMULA(TO_DATE(IFNA(VLOOKUP(A2:A, 
 SORTN(SORT(FILTER({Sheet1!E2:E, Sheet1!A2:A}, 
 NOT(COUNTIF({A2:A&B2:B; A2:A&C2:C; A2:A&D2:D}, Sheet1!E2:E&Sheet1!A2:A))), 2, 0),
 999^99, 2, 1, 1), 2, 0))))

etc...