Return latest date based on matching criteria

[senthilvenkatesh]

Hi,

I am looking for a formula that returns the latest Sale date for a each model of car. Below is sample data which I am trying to use the formula. Can someone help me with correct formula. I tried with below formula, but not successfu
l.
=MAX(INDEX($A$1:$C$40,MATCH(A2,$A$1:$C$40,0),3),1)


Make Model Sale Date
Audi A4 11-Jan-14
Audi A4 quattro 12-Jan-14
Audi A4 quattro 3-Jan-14
Audi A5 Cabriolet 14-Jan-14
Audi A5 Cabriolet q 15-Jan-14
Audi A4 16-Jan-14
Audi A4 quattro 17-Jan-14
Audi A4 quattro 18-Jan-14
Audi A6 quattro 19-Jan-14
Audi A4 10-Jan-14
Audi A4 quattro 09-Jan-14
Audi A4 quattro 10-Jan-14
Audi A4 11-Jan-14
Audi A4 quattro 11-Jan-14
Audi A4 quattro 11-Jan-14
Audi A8L 11-Jan-14
Audi A8L 11-Jan-14

[zbor]

If those in (I guess?!) C column are dates then in D1 use:

Please Login or Register  to view this content.

Confirm with ctrl+shift+enter (not just enter) and pull formula down.

[senthilvenkatesh]

If those in (I guess?!) C column are dates then in D1 use:

Please Login or Register  to view this content.

Confirm with ctrl+shift+enter (not just enter) and pull formula down.

Hi,

Excellecnt formula. I am getting the desired result. Thanks a ton for your quick reply.

[sktneer]

Try this.....

In E2 (Array Formula to get unique models. Array formula requires confirmation with Ctrl+Shift+Enter instead of just Enter.)

Please Login or Register  to view this content.

and copy down until you get blank cells.

In F2 (Again an Array Formula requires CSE confirmation)

Please Login or Register  to view this content.

and copy down.

For more details see the attached sheet.