Index help please! [SOLVED]

[exceljockey]

Hi


I have a problem here which im sure somebody will know the solution to:


http://i.imgur.com/WOgT9Hm.png


If you look at the image, amongst this table, there are different reference numbers. Two different letters get sent out amongst the same reference numbers - one in print and one in brail.


What i want to see, is what the last date that a letter was sent out for print, and for brail, but under a certain reference.


For example, i want to choose to look for reference A0002 want to know the last date that brail was sent out, and the last date that printed was sent out.


What I did was find the largest date amongst the table and returned its corresponding type and reference number, but I want to go back and start from the reference number, and work out what the last date was for brail and printed. I feel like i am nearly there but having a brain jam at the moment and cant work it out.


I then got this: =(INDEX($D$4:$D$28,(MATCH(G6,IF($B$4:$B$28=G7,$C$4:$C$28),0))))


This then returns the FIRST date rather than the last. How do I make it so that it picks up the last date?


Thanks

EJ.

Thanks, EJ

 

[Faseeh]

With your data in B4:C19 excluding headers, use following formula:

=SUMPRODUCT(MAX((B4:B19="A00002")*(C4:C19="BRAIL")*(D4:D19)))

Hope that helps,


Regards,

 

[Hui]

Search for Maxif in the Google Custom Search box at the top right of this page

 

[exceljockey]

Thanks for your help guys, I found the solution thanks to Hui's pointing in the right direction - answer lied in Maxif.


Thank you very much!