VLOOKUP and Return Last 2 Values

N

Nick

Here is an example table...
Date | Name | Score |
5/20/2009 | Nick | 95 |
5/21/2009 | Jon | 96 |
5/22/2009 | Jon | 97 |
5/23/2009 | Jon | 98 |
5/24/2009 | Jon | 99 |
5/25/2009 | Nick | 72 |
5/26/2009 | Jon | 45 |
5/27/2009 | Nick | 30 |

I would like to create a summary page that has "Nick" and "Jon" and returns
their last 2 most recent scores to track their current trend. So for Nick,
the result would be:
Nick
Most Recent: 30
Second Most Recent: 72
Jon
Most Recent: 45
Second Most Recent: 99

There will never be duplicate dates. The primary key is basically the date
column. I will continue to add dates on the data dump table so it will need
to have the opportunity to expand. I've searched through this for a while
and can't find my answer. Any help is greatly appreciated!
 
T

Teethless mama

=VLOOKUP(LARGE(IF($B$2:$B$9="Nick",$A$2:$A$9),ROWS($1:1)),$A$2:$C$9,3,0)

ctrl+shift+enter, not just enter
copy down
 
T

Teethless mama

Non array formula

In D2: =LOOKUP(2,1/(B2:B9="Nick"),C2:C9)
In D3: =LOOKUP(2,1/((B2:B9="Nick")*(C2:C9<>D2)),C2:C9)

Just press Enter
 
N

Nick

Teethless, worked perfectly! I'm trying to understand the logic, though, and
not really understanding. Can you explain what this function is actually
doing? I think the issue I have is with the 1/*** part. Doesn't make sense.
 
N

Nick

I tried that but I could not figure out a way for it to return the most
recent value for each person let alone the second to last value for each
person. Teethless's method worked flawlessly.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top