CODEWITHSUNDEEP
excelexcel-formula
user1565946
user1565946

Reputation: 39

Lookup the second largest value in a specific set of column values (Without creating a new column)

I have a table that looks something like this

Employee Date
Sean     21-07-2012
Peter    19-08-2012
Tammy    19-08-2012
Sean     14-07-2012
Sean     07-07-2012
Mickey   09-07-2012
Mickey   24-07-2012
Jean     27-07-2012
Jean     13-07-2012
Jean     07-07-2012
Victor   17-08-2012
Victor   10-08-2012

Let's say I want to get the second largest date for Jean. I found the following tutorial: http://www.dailydoseofexcel.com/archives/2009/04/21/vlookup-on-two-columns/

and I tried to use the top method this way:

=SUMPRODUCT((A:A='Jean')*LARGE(B:B,2))

I am getting a large number which doesn't make sense. Some help would be greatly appreciated.

Thanks

Upvotes: 3

Views: 2112

Answers (1)

danielpiestrak
danielpiestrak

Reputation: 5439

Use the following formula instead: 

=LARGE(IF(A:A="Jean",B:B),2)

(Note this is an array formula, so you need to push ctrl+shift+enter after typing it in)

What this does is it takes column A, and if it contains "Jean" then it gives the corresponding date from column B into the Large formula. This way the Large formula is only working with the dates that meet your requirements.

Upvotes: 4

Related Questions