Search code examples
excelexcel-formulamaxvlookupmaxifs

Find a value of a cell next to the max value

I have a sheet with 3 columns: Employee, Role, Start Date

Each employee has multiple records (his history in the company).

I have a list of all the employees (distinct list) and I want to have a column of the current role for each employee - which means the role of the max date for each employee. I was trying vlookup, and ={max(if...} approaches but couldn't get it right.

Here is a screenshot of an example. As you can see I know how to get the latest date (max) but I can't find a way to get the relevant role for that date.

enter image description here

Solution

  • An Array Formula

    Select cell I2 and since this is an array formula, you paste the formula into the formula bar and hold down the left ALT and SHIFT keys and then press ENTER.

    =INDEX(B$2:B$45,MATCH(G2&H2,A$2:A$45&C$2:C$45,0))

    The formula will look like this in the formula bar: 

    {=INDEX(B$2:B$45,MATCH(G2&H2,A$2:A$45&C$2:C$45,0))} 'Do NOT use this!!!

    with the curly braces indicating that it is an array formula.