1

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

VBasic2008
  • 44,888
  • 5
  • 17
  • 28
Doron Goldberg
  • 643
  • 1
  • 9
  • 23
  • We'll have to see at least some sample data and the formula you've tried in order to help you. Without understanding the data layout better, it's impossible to give an example solution. – PeterT Oct 28 '18 at 16:34
  • Thanks @PeterT, I have added a screenshot with my current status. – Doron Goldberg Oct 28 '18 at 18:16

1 Answers1

1

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.

VBasic2008
  • 44,888
  • 5
  • 17
  • 28