Find maximum value from two tables with criteria

[K1600]

I have two tables (Table2 and Table3), both with multiple columns but for the purpose of this let’s just say each has 3 columns, 'Date', 'Person' and 'Pass/Fail'.

Each person could appear in one or both of the tables, but the dates will be different in each. I need to return the highest date for a given person (named in cell A1) and where "Pass" is in the Pass/Fail column irrespective of which table they are in.

I have sussed this using MAXIFS for a single table, but I can't make it work across the second table.

If anyone has any suggestions, I would be most grateful.

Thanks in advance.

 

[saboh12617]

... Sorry, as you see i wrote a ";" instead of "," after dateFound. It's because i'm on French Excel instead of english and the splitters are different.
Please correct like so

=LET(
  dateFound,
  EDATE(
  (MAX(MAXIFS('New Duties Sheet - DO NOT USE.xlsx'!Table2[Date],
  'New Duties Sheet - DO NOT USE.xlsx'!Table2[Person],"*"&[@[Person]]&"*",
  'New Duties Sheet - DO NOT USE.xlsx'!Table2[Pass / Fail],"Pass"),
  MAXIFS('New Duties Sheet - DO NOT USE.xlsx'!Table3[Date],
  'New Duties Sheet - DO NOT USE.xlsx'!Table3[Person],"*"&[@[Person]]&"*",
  'New Duties Sheet - DO NOT USE.xlsx'!Table3[Pass / Fail],"Pass"))),12*O$8)
 IF(OR([@[Person]]="", YEAR(dateFound)<1905), "", dateFound)
)

 

[K1600]

... Sorry, as you see i wrote a ";" instead of "," after dateFound. It's because i'm on French Excel instead of english and the splitters are different.
Please correct like so

=LET(
  dateFound,
  EDATE(
  (MAX(MAXIFS('New Duties Sheet - DO NOT USE.xlsx'!Table2[Date],
  'New Duties Sheet - DO NOT USE.xlsx'!Table2[Person],"*"&[@[Person]]&"*",
  'New Duties Sheet - DO NOT USE.xlsx'!Table2[Pass / Fail],"Pass"),
  MAXIFS('New Duties Sheet - DO NOT USE.xlsx'!Table3[Date],
  'New Duties Sheet - DO NOT USE.xlsx'!Table3[Person],"*"&[@[Person]]&"*",
  'New Duties Sheet - DO NOT USE.xlsx'!Table3[Pass / Fail],"Pass"))),12*O$8)
 IF(OR([@[Person]]="", YEAR(dateFound)<1905), "", dateFound)
)

I did wonder about the ;

With the , in instead, I am now getting "You've entered too few arguments for this function". It is then placing my cursor between the last too close brackets on the 'name_value1' part of the syntax.

 

[saboh12617]

Hrmm sorry,

Since i do not have the data i did not test the formula (which i usually do), and it was missing a comma before the IF, to separate it from the EDATE.

Like so

=LET(
  dateFound,
  EDATE(
  (MAX(MAXIFS('New Duties Sheet - DO NOT USE.xlsx'!Table2[Date],
  'New Duties Sheet - DO NOT USE.xlsx'!Table2[Person],"*"&[@[Person]]&"*",
  'New Duties Sheet - DO NOT USE.xlsx'!Table2[Pass / Fail],"Pass"),
  MAXIFS('New Duties Sheet - DO NOT USE.xlsx'!Table3[Date],
  'New Duties Sheet - DO NOT USE.xlsx'!Table3[Person],"*"&[@[Person]]&"*",
  'New Duties Sheet - DO NOT USE.xlsx'!Table3[Pass / Fail],"Pass"))),12*O$8),
 IF(OR([@[Person]]="", YEAR(dateFound)<1905), "", dateFound)
)

 

[K1600]

P

Hrmm sorry,

Since i do not have the data i did not test the formula (which i usually do), and it was missing a comma before the IF, to separate it from the EDATE.

Like so

=LET(
  dateFound,
  EDATE(
  (MAX(MAXIFS('New Duties Sheet - DO NOT USE.xlsx'!Table2[Date],
  'New Duties Sheet - DO NOT USE.xlsx'!Table2[Person],"*"&[@[Person]]&"*",
  'New Duties Sheet - DO NOT USE.xlsx'!Table2[Pass / Fail],"Pass"),
  MAXIFS('New Duties Sheet - DO NOT USE.xlsx'!Table3[Date],
  'New Duties Sheet - DO NOT USE.xlsx'!Table3[Person],"*"&[@[Person]]&"*",
  'New Duties Sheet - DO NOT USE.xlsx'!Table3[Pass / Fail],"Pass"))),12*O$8),
 IF(OR([@[Person]]="", YEAR(dateFound)<1905), "", dateFound)
)

Perfect, thank you very much.

 

[K1600]

Hrmm sorry,

Since i do not have the data i did not test the formula (which i usually do), and it was missing a comma before the IF, to separate it from the EDATE.

Like so

=LET(
  dateFound,
  EDATE(
  (MAX(MAXIFS('New Duties Sheet - DO NOT USE.xlsx'!Table2[Date],
  'New Duties Sheet - DO NOT USE.xlsx'!Table2[Person],"*"&[@[Person]]&"*",
  'New Duties Sheet - DO NOT USE.xlsx'!Table2[Pass / Fail],"Pass"),
  MAXIFS('New Duties Sheet - DO NOT USE.xlsx'!Table3[Date],
  'New Duties Sheet - DO NOT USE.xlsx'!Table3[Person],"*"&[@[Person]]&"*",
  'New Duties Sheet - DO NOT USE.xlsx'!Table3[Pass / Fail],"Pass"))),12*O$8),
 IF(OR([@[Person]]="", YEAR(dateFound)<1905), "", dateFound)
)

@saboh12617 (or anyone else that can assist)

I am using the formula you suggested and it is working great.

I have now been asked to return the top two dates across the same tables with the same criteria. I want to pull the two dates into two seperate cells, is there a way to do this with a modification to this formula or am I going to need something completly different? Thinking ahead, I could probably do with including a way to deal with the situation where there isn't two dates in the table to return.

Thanks in advance.

 

[saboh12617]

@saboh12617 (or anyone else that can assist)

I am using the formula you suggested and it is working great.

I have now been asked to return the top two dates across the same tables with the same criteria. I want to pull the two dates into two seperate cells, is there a way to do this with a modification to this formula or am I going to need something completly different? Thinking ahead, I could probably do with including a way to deal with the situation where there isn't two dates in the table to return.

Thanks in advance.

Hello, then using an array formula like the one @Fluff proposed would make it easier as you can take any n-first elements from the sorted dates.

However I'm struggling to visualize things, some sample tables and desired outputs would be of great help.

Please find below a proposition based on @Fluff's first post, with the two biggest dates matching all criterias (name in A1, "Pass" in third column, dates>1905) returned. If only 1 or none are found, it should adapt.

=LET(
  v,VSTACK(Table2,Table3),
  datesFound,TAKE(SORT(FILTER(v,(INDEX(v,,2)=A1)*(TAKE(v,,-1)="Pass")),1,-1),2),
  FILTER(datesFound,YEAR(TAKE(datesFound,,1))>1905,""))