Hi, I am encountering some problems with a vlookup formula where I am trying to join data based on a common field in two worksheets.

If you look at the attached image, you will see that I am trying to show the date of the last action in the report worksheet by looking up the client ID from the report worksheet in the action worksheet.

Excel-VLOOKUP.jpg

The formula I have used is
=VLOOKUP(A2,Action!A:C,3,TRUE)

This works for the first row in the report worksheet, where it correctly displays the 01 May 2013 as that is the corresponding date for that client ID in the action worksheet.

The problem is that not all client IDs will have an action, and where this is the case (in rows 3,4,5,7,8,9 & 10) it is incorrectly showing the date from the last result it can find from the rows above.

Also, some clients may have more than 1 action, so how do I show the earliest date when this is the case? E.g. client ID 2958 has 2 actions in the action worksheet, but the date shown in the report worksheet is the date furthest in the future - I want the earliest date.

A) How do I stop dates being shown incorrectly?
B) How do I show the earliest action date if there are 2 or more dates for a client ID in the action worksheet (e.g. rows 3 & 4)?

Many thanks in advance for any help with this!
Steve