Find maximum value from two tables with criteria

K1600

Board Regular
Joined
Oct 20, 2017
Messages
196
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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hello,
What about taking the max of the two maxifs
Excel Formula:
=MAX(MAXIFS(Table1[Date], Table1[Pass/Fail], "Pass"),  MAXIFS(Table2[Date], Table2[Pass/Fail], "Pass"))

If the tables are of the same structure (ie. identical columns), you can just use VSTACK and Maxifs on this stack (With Date in column 1 and Pass/Fail in column 3).
Excel Formula:
=LET(allScores, VSTACK(Table1, Table2),
  MAXIFS(CHOOSECOLS(allScores, 1), CHOOSECOLS(allScores, 3), "Pass")
)
 
Upvote 0
How about
Excel Formula:
=LET(v,VSTACK(Table2,Table3),TAKE(SORT(FILTER(v,(INDEX(v,,2)=A1)*(TAKE(v,,-1)="Pass")),1,-1),1,1))
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
Further help if I may please....

I am now using the following formula but it is returning 00/01/1900 (or 31/12/1904 when 5 years are added via EDATE function) and I don't want these spurious dates showing. Is there an amendment I can make to the formula to stop this, either by returning a 0 or preferably, nothing at all?

Excel Formula:
=IF([@[Person]]="","",

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))

O8 has a number in it for the number of years to add to the returned date.

Thanks in advance.
 
Upvote 0
Hello,
You could add a check on the date's year 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

This is giving me an error of "The first argument of LET must be a valid name".
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,135
Members
453,642
Latest member
jefals

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