Return latest date Part repair information from an array

[Martin Chamberlin]

I have a list of spare part repairs. Column A Repair date, Column B Part reference, Column C repair information. The same Part reference can appear in the list at subsequent repair dates.

How would I look up the array and return the latest date repair information from Column C.

Help appreciated

M

ps dummy file attached for clarification. Red text is the wanted result.

[micope21]

Hi

Try this Cell E2

Please Login or Register  to view this content.

Cell F2

Please Login or Register  to view this content.

This is a array formula!! Copy both formulas and paste it excel wookbook!! Then press Ctrl+Shift+Enter same time!! Not just Enter ok.

Regard
micope21

[Tony Valko]

Assuming the latest date is the bottom-most date...

Data Range

	A	B	C	D	E	F
1	Repair Date	Part Ref	Repair information
2	2/1/2013	Shaft 1	Torn	Shaft 1	9/26/2013	Snapped
3	6/7/2013	Key 2	Damaged
4	7/11/2013	Drive 4	Broken
5	7/14/2013	Shaft 1	Bent
6	9/26/2013	Shaft 1	Snapped
7	12/12/2013	Drive 1	Jammed

This formula entered in E2:

=LOOKUP(2,1/(B2:B7=D2),A2:A7)

Format as Date

This formula entered in F2:

=LOOKUP(2,1/(B2:B7=D2),C2:C7)