CODEWITHSUNDEEP
excelmaxwhere-clausevlookup
Paul
Paul

Reputation: 69

MAX function with VLOOKUP

I need to be able to see what the last date is for each reference and what gender (if any) is associated with the reference and the last date. I need to perform this in a function rather than VBA. My Data:

A0001   11/06/2002  F
A0001   21/02/2012  F
A0001   11/06/2002  M
A0001   21/02/2012  M
A0001   21/02/2012  
A0002   20/05/2002  F
A0002   20/05/2002  M
A0002   20/05/2002  F
A0002   20/05/2002  M
A0002   11/04/2007  F
A0002   11/04/2007  M

I've been banging my head against the Lookup, Address, Index, Match, Max functions all morning and now have a headache, please put me out of my misery.

Upvotes: 0

Views: 1683

Answers (1)

JamTay317
JamTay317

Reputation: 1017

First off if you can I would use a Pivot Table, Drag the Item Number into the rows, the Gender into the rows, and the date into the values. Click on the arrow next to date in the values section. Choose value Field settings, Choose Max, go to number format, and finally choose date. That will do as you ask.

enter image description here

Now, if you must use formulas (This is amusing that your Item number is in column A, your date is in Column B, and your Gender is in Column C)

=MAX(IF($A$2:$A$11=$E2,IF($C$2:$C$11=F$1,$B$2:$B$11)))

which will require Control Shift Enter.

enter image description here

Upvotes: 1

Related Questions