Hi!
I've been using a MAX/IF array function in some of my sheets. Recently I read about using MAXIFS instead, but I can't get it to return the same result as my array. I'm not sure what I'm doing wrong. Basically I am using the function in an accounting report. It uses a store number to filter the MAX result from 2 other workbooks, returning the date of the most recent activity for the store.
Here's the array:
=MAX(IF('W:\CoreData\[core data 2018 YTD.xlsx]Sheet1'!$K:$K=C2,'W:\CoreData\[core data 2018 YTD.xlsx]Sheet1'!$B:$B,(IF('W:\CoreData\[core data YTD.xlsx]Sheet1'!$K:$K=C2,'W:\CoreData\[core data YTD.xlsx]Sheet1'!$B:$B))))
Column K in the other book is the list of store numbers. Cell C2 is the store number on the current sheet that is being used as a filter. Column B in the other book is the list of dates. There are two workbooks listed in the function because if the store is not found in the 2018 book, it looks in the 2017 book.
Here's the MAXIFS function I'm experimenting with:
=MAXIFS('W:\CoreData\[core data 2018 YTD.xlsx]Sheet1'!$B:$B,'W:\CoreData\[core data 2018 YTD.xlsx]Sheet1'!$K:$K,C2)
I haven't added the second workbook to the MAXIFS function yet, as I'm not sure how to incorporate it into an IF with the MAXIF. That problem is secondary.
The main problem I'm having is that the array and the MAXIFS are returning two different results. The array function works, it's just slow. The MAXIFS function is just returning "#VALUE!".
