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