SOLVED

Lookup / Index / Match - look up values corresponding to MAX date

%3CLINGO-SUB%20id%3D%22lingo-sub-1409503%22%20slang%3D%22en-US%22%3ELookup%20%2F%20Index%20%2F%20Match%20-%20look%20up%20values%20corresponding%20to%20MAX%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1409503%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMe%20again%20-%20I%20am%20really%20struggling%20with%20this%20report%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20each%20Account%20name%20in%20column%20B%20(indirect%20tab)%2C%20I%20need%20columns%20A%2C%20C%2C%20D%2C%20E%2C%20F%20and%20G%20to%20lookup%20the%20corresponding%20info%20where%20its%20the%20MAX%20definitive%20end%20date%20for%20that%20account%20name.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20lookup%20should%20take%20values%20from%20tab%20'salesforce%20contracts%2020th%20May'%20(currently%20it%20points%20to%20a%20pivot%20but%20i'm%20trying%20to%20avoid%20pivots%20and%20to%20instead%20navigate%20the%20raw%20data%20directly%20as%20much%20as%20possible).%20Here%20the%20account%20names%20are%20repeated%20as%20they%20have%20several%20contracts%20each.%20I%20want%20the%20information%20in%20the%20'Indirect'%20tab%20to%20correspond%20to%20the%20latest%20definitive%20end%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much%20for%20your%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1409503%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1410820%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20%2F%20Index%20%2F%20Match%20-%20look%20up%20values%20corresponding%20to%20MAX%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1410820%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668941%22%20target%3D%22_blank%22%3E%40JennySommet%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20Simply%20you%20can%20sort%20the%20column%20Definitive%20Ending%20Date%20Newest%20to%20Oldest%20and%20do%20a%20vlookup%20on%20account%20name%20to%20fetch%20all%20details.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1411313%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20%2F%20Index%20%2F%20Match%20-%20look%20up%20values%20corresponding%20to%20MAX%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1411313%22%20slang%3D%22en-US%22%3EThanks!%20I%20guess%20the%20only%20thing%20I%20can't%20do%20is%20pick%20out%20the%20unique%20account%20names%20-%20I%20have%20to%20get%20those%20from%20a%20pivot.%3C%2FLINGO-BODY%3E
Contributor

Hello,

 

Me again - I am really struggling with this report  

 

For each Account name in column B (indirect tab), I need columns A, C, D, E, F and G to lookup the corresponding info where its the MAX definitive end date for that account name.

 

The lookup should take values from tab 'salesforce contracts 20th May' (currently it points to a pivot but i'm trying to avoid pivots and to instead navigate the raw data directly as much as possible). Here the account names are repeated as they have several contracts each. I want the information in the 'Indirect' tab to correspond to the latest definitive end date.

 

Thanks so much for your help!

 

2 Replies
Highlighted
Best Response confirmed by JennySommet (Contributor)
Solution

@JennySommet 

Hi, Simply you can sort the column Definitive Ending Date Newest to Oldest and do a vlookup on account name to fetch all details.

Highlighted
Thanks! I guess the only thing I can't do is pick out the unique account names - I have to get those from a pivot.