| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP and Return Last 2 Values
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP and Return Last 2 Values
=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 "Nick" wrote: 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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP and Return Last 2 Values
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 "Nick" wrote: 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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP and Return Last 2 Values
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. "Teethless mama" wrote: 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 "Nick" wrote: 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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP and Return Last 2 Values
Excel 2007 PivotTable
No formulas of any kind needed. Added data expands results automatically. http://www.mediafire.com/file/cozimjrz5gn/07_10_09.xlsx |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP and Return Last 2 Values
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. "Herbert Seidenberg" wrote: Excel 2007 PivotTable No formulas of any kind needed. Added data expands results automatically. http://www.mediafire.com/file/cozimjrz5gn/07_10_09.xlsx |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using H/VLOOKUP to return multiple values | Excel Discussion (Misc queries) | |||
| Vlookup Return Multiple Values | Excel Discussion (Misc queries) | |||
| How do I return Multiple values using VLookup? | Excel Worksheet Functions | |||
| vlookup one value and return multiple values | Excel Discussion (Misc queries) | |||
| vlookup function return all values | Excel Worksheet Functions | |||
Linear Mode
