look up maximum date and state

[sampal]

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


[TABLE="width: 195"]
<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>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 68, bgcolor: transparent"]NSW[/TD]
[TD="width: 64, bgcolor: transparent"]QLD[/TD]
[TD="width: 64, bgcolor: transparent"]VIC[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]50[/TD]

[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]STATE[/TD]
[TD="bgcolor: transparent"]DATE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NSW[/TD]
[TD="bgcolor: transparent, align: right"]1/01/2019[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]QLD[/TD]
[TD="bgcolor: transparent, align: right"]3/01/2019[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]VIC[/TD]
[TD="bgcolor: transparent, align: right"]4/01/2019[/TD]
[TD="bgcolor: transparent, align: right"]50[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

Maybe something like this.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C
1	NSW	QLD	VIC
2	10	20	50
3
4	STATE	DATE
5	NSW	10/12/2008	25
6	QLD	3/1/2019	20
7	VIC	4/1/2019	50
8	VIC	3/12/2018	30
9	NSW	1/1/2019	10
Sheet

 

[sampal]

What is the "I" , how do I get that ?

 

[sampal]

also could you please break down this formula, I am trying hard to understand how this works ?

 

[AhoyNC]

The | symbol is above the ENTER key that also has the \ symbol. It's only use is to put a character (actual almost any character would do) between the state and date. In this case it's probably not needed. The example below shows why you may need it in some cases.
Column C below gives two unique alphanumeric data. Where column D returns the same alphanumeric data.

Excel Workbook
	C
2	AB1|456
Sheet

Formula breakdown.
This part returns the max date if the state equals cell A1 in this example.

MAX(IF($A$5:$A$9=A$1,$B$5:$B$9))

returns
1/1/2019

This will return state and max date.

A$1&"|"&MAX(IF($A$5:$A$9=A$1,$B$5:$B$9))

returns
NSW|1/1/2019

MATCH will return the row number where it finds the above.

MATCH(A$1&"|"&MAX(IF($A$5:$A$9=A$1,$B$5:$B$9)),$A$5:$A$9&"|"&$B$5:$B$9,0)

returns row 5 in this case.

MATCH just tells the INDEX what row to bring back the data from in the table, so in this case row 5 of column C in the data equals 10.

 

[AhoyNC]

The example in the post above for using the | symbol should have been.

Excel Workbook
	A	B	C	D
1			Different	Same
2	AB1	456	AB1|456	AB1456
3	AB	1456	AB|1456	AB1456
Sheet

 

[sampal]

Superb ! well done... Thank you