I am looking to find the max date from 2 tables of the matching value AAA. Result should be 31/12/2021
First table
| REF NUMBER | DATE |
|---|---|
| AAA | 01/01/2000 |
| AAA | 01/01/2010 |
| BBB | 01/01/2005 |
2nd table
| REF NUMBER | DATE |
|---|---|
| AAA | 31/12/2021 |
| AAA | 31/12/2015 |
| BBB | 01/01/2005 |
This is my try, using matrix/array validation, but the result take only one table into account.
{=MAX(IF(A13='table1'!A:A,'table1'!B:B),IF(A13='table2'!A:A,'table2'!B:B))}
CodePudding user response：
Your formula is correct. You should check your data to make sure it's in the correct location and make sure that all dates are stored as dates and not as text.
CodePudding user response：
As mentioned, your formula seems fine. As an alternative:
Formula in G4:
=VLOOKUP("AAA",SORT(VSTACK(A2:B4,D2:E4),2,-1),2,0)