Index Match Most Current Date

[boll55]

is there any formula that i can use that will provide me with the most current termination date of a specific driver

i was thinking something along the lines of

=IFERROR(INDEX(I:I,MATCH(L2,MAX(H:H,0))),"-")

this obviously doesnt work so any ideas on how to get where i need to be!?!?!?

 

[lrobbo314]

=iferror(index(i:i,match(max(h:h),h:h,0)),"-")

 

[boll55]

but will that pull the date for just a specific driver in the column next to it? thats what the L2 was referring to

 

[Jonmo1]

Try this array formula enterd with CTRL + SHIFT + ENTER

=MAX(IF(H1:H1000=L2,I1:I1000))

 

[boll55]

That doesn't work. Here is a better description of what I am wanting I think:

Column L is a list of unique values that are pulled from column H and column I are the dates that go with the drivers in column H. I need the cell L2, L3, L4 ect to look up the driver in that cell in the column H and return the corresponding value from column L.

index match works great except some of the drivers are listed more than once in column H and it pulls the first date it finds which is not the most current termination date

 

[Jonmo1]

It works for me, the only thing I left out was the Absolute references $$.
That might have thrown off the ranges as you dragged down.

Also, it's VERY IMPORTANT that you press CTRL + SHIFT + ENTER when you enter the formula..

Excel Workbook
	H	I	J	K	L	M
2	Fred	03 March 2013			Jon	29 April 2013
3	Fred	28 March 2013			Fred	06 May 2013
4	Fred	05 April 2013			Mike	18 April 2013
5	Fred	19 April 2013
6	Fred	27 April 2013
7	Fred	04 May 2013
8	Fred	06 May 2013
9	Jon	14 March 2013
10	Jon	26 March 2013
11	Jon	03 April 2013
12	Jon	06 April 2013
13	Jon	07 April 2013
14	Jon	24 April 2013
15	Jon	29 April 2013
16	Mike	14 February 2013
17	Mike	20 February 2013
18	Mike	08 March 2013
19	Mike	19 March 2013
20	Mike	11 April 2013
21	Mike	14 April 2013
22	Mike	18 April 2013
Sheet1

 

[boll55]

Nailed it. Set up the right ranges and works wonderfully. Thanks

 

[Jonmo1]

Glad to help, thanks for the feedback.