Find maximum value from two tables with criteria

K1600

Board Regular
Joined
Oct 20, 2017
Messages
197
Office Version
  1. 365
Platform
  1. Windows
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.
 
... 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
Excel Formula:
=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)
)
 
Upvote 0
... 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
Excel Formula:
=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.
 
Upvote 0
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

Excel Formula:
=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)
)
 
Upvote 0
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

Excel Formula:
=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.
 
Upvote 0
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

Excel Formula:
=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.
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,919
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top