Hi I need a formula to look up maximum date and corresponding state
In below case it should look at state , eg VIC and also maximum date 04/01/2019, giving answer 50
<colgroup><col width="64" style="width: 48pt;">
<col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;">
<col width="64" style="width: 48pt;" span="2">
<tbody>
</tbody>
In below case it should look at state , eg VIC and also maximum date 04/01/2019, giving answer 50
NSW | QLD | VIC | |
50 | |||
STATE | DATE | ||
NSW | 1/01/2019 | 10 | |
QLD | 3/01/2019 | 20 | |
VIC | 4/01/2019 | 50 |