Formula help

[iluvsafc]

Hi all
I have a problem that I’m stuck with and I cannot for the life of me come up with a formula that works!

I have a data set that is duplicating on ID number as there is more than one expiry date per ID.

I’m after a formula that would look up each ID number in column A but bring back the most recent expiry date.

	A	B	C	D
1	ID Number	Expiry Date	Formula to bring back most recent expiry date per ID number????	Expected result
2	01	01/01/2014		01/01/2016
3	01	01/01/2015		01/01/2016
4	01	01/01/2016		01/01/2016
5	02	01/01/2014		01/01/2016
6	02	01/01/2015		01/01/2016
7	02	01/01/2016		01/01/2016
8	03	01/01/2014		01/01/2016
9	03	01/01/2015		01/01/2016
10	03	01/01/2016		01/01/2016

<tbody>
</tbody>

My ideal result would be a separate pivot table (or something along the lines!) that would show the following –

01	01/01/2014
02	01/01/2015
03	01/01/2016

<tbody>
</tbody>

Any help would be greatly appreciated!
Cheers
Lee

 

[gaz_chops]

Try this, enter as array, Ctrl, Shift & Enter

=MAX(IF($B$2:$B$10=$B2,$C$2:$C$10))

 

[Special-K99]

in A11 put 1
in A12 put 2
in A13 put 3

in B11
=MAX(IF(B$1:B$9=A11,C$1:C$9,))
Array formula, use Ctrl-Shift-Enter

copy to B12 and B13