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

[JennySommet]

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 · Accepted 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.

[#3]

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.

[#4]

please help with this:
Enter a nested INDEX and MATCH function in cell K7 that examines the range B6:H25 and returns the corresponding employee information based on the match values in cell J7 and cell K6. Note K6 contains a validation list that can be used to select various lookup categories. Use the Data Validation list in cell J7 to select Employee_ID 31461 and select Salary in cell K6 to test the function.

[#5]

Try:
=Index(B6:H25,Match(J7,A6:A25,0),Match(K6,B5:H5,0))

[#6]

thanks a bunch it works like charm.