vlookup

[realtop17]

using vlookup how can i get the latest date if the table array have the same or duplicate number

EXAMPLE.

[pre]

Serial	                      Part no.	      contorl #	  date
R20120404	        P04-060252-00311-E000	ctrl 10	4/1/2012
R20120484	        P04-060160-00111-6000	ctrl 10	4/1/2012
21524118826CC5201958	P04-181560-10311-C000	ctrl 10	4/1/2012
21524118826CC5202078	P04-181560-10311-C000	ctrl 10	4/1/2012
21524131476CC5201761	P04-060340-10111-C000	ctrl 10	4/1/2012
R20120404	        P04-060252-00311-E000	ctrl 25	5/17/2012
R20120484	        P04-060160-00111-6000	ctrl 25	5/17/2012
21524118826CC5201958	P04-181560-10311-C000	ctrl 25	5/17/2012
21524118826CC5202078	P04-181560-10311-C000	ctrl 25	5/17/2012
21524116506CC5202301	P04-070154-10211-C100	ctrl 25	5/17/2012

[/pre]

 

[realtop17]

using vlookup how can i get the latest date if the table array have the same or duplicate number


ex.

serial date

123 5/12/10

124 5/12/10

125 5/12/10

123 5/17/12

124 5/17/12


i try to lookup these serials but the old date are captured please help on this.


thank you.

 

[fred]

I use pivot table and set the "value field settings" to "Max".

 

[realtop17]

thank you fred but im beginners in excel where can i find value field setting

 

[Luke M]

Once you have your Pivot Table, right click on the data field, field settings, select "Max", ok out.

 

[Montrey]

This formula will work Realtop.


=INDEX(L3:L4,MATCH(LARGE(K3:K4,1),K3:K4))


This is an array formula so use Ctrl+shift+enter

Also adjust the ranges accordingly.

 

[realtop17]

thank you verry much guys now i can use it.

 

[Luke M]

Glad we could help. =)