How do I make Excel refer back to the last instance of a specific name?

**junkemailmenot** · 05-20-2009, 07:09 AM

I have a spreadsheet tracking work assignments for 7 employees. Column F has their names, column G has the date they begin an assignment, and column H has the date they finish. I want a formula for column G that will look at the name in column F, find the last instance of that name, and put the finishing date from column H into this new instance of the name, in column G.

1.....F..........G..........H
2.....John.....1 Jan....15 Jan
3.....Joe.......3 Jan....11 Jan
4.....Jack.....4 Jan....24 Jan
5.....John.....15 Jan..22 Jan
6.....John.....???

I want G6 to find the last time that John appeared in column F, which happens to be line 5, and automatically take the date from H5 and put it in G6.

**DonkeyOte** · 05-20-2009, 07:14 AM

You can use LOOKUP

G6: =LOOKUP(2,1/($F$2:$F5=$F6),$H$2:$H5)

**junkemailmenot** · 05-21-2009, 04:28 AM

Thank you SO MUCH.

Now I need to make Excel find the last date for a specific item.

Using the same example:

1.......F.......G.......H
2.......John....1 Jan...15 Jan
3.......Joe.....3 Jan...11 Jan
4.......Jack....4 Jan...24 Jan
5.......John....15 Jan..22 Jan
6.......John....22 Jan..3 Feb
7.......Joe.....11 Jan..19 Jan
8.......Jack....24 Jan..4 Feb
9.......???
10......???

I want F9 to find which employee (Joh, Joe or Jack) finished their last appearance in the chart earliest, which happens to be John in line 6 in this case, and automatically put that name in F9. For F10, the same formula would automatically put Jack.

Any ideas?

**zbor** · 05-21-2009, 05:33 AM

Isn't Jack last finnished in row8?

It can be found with this formula...

=INDEX($D$8:D14;MATCH(MAX($F$8:F14);$F$8:F14;0); )

**DonkeyOte** · 05-21-2009, 08:18 AM

To be honest I can't think of an easy way to do this without helpers... I suspect there may well be a very clever Frequency Array or equivalent approach but beyond my abilities I'm afraid... I will ask a couple of others to have a look.