Matching 2nd & 3rd values

[johan.retief]

Hi

I have gone through a quite a few discussions and could not figure out
how to solve my problem.

I have two workbooks.

The first is a list of clients with specific values for each client.
Attributes:
- Each client might have three or more instances.
- Each instance is separated by a specific date (like a company year-
end).

A B C
1 > Jones 2006 Completed
2 > Jones 2007 Completed
3 > Jones 2008 Not Completed
4 > Barnes 2006 Completed
5 > Barnes 2007 Not Completed
6 > Barnes 2008 Not Completed
7 > Smith 2006 Not Completed
8 > Smith 2007 Not Completed
9 > Smith 2008 Not Completed
10 > Smith 2006 Completed

The other workbook (progress book) is used by staff members to update
the status ("Completed" / "Not Completed") of the client.
This workbook has similar fields to the above, but only contains a
list of clients already allocated to the staff members and will
therefore only have one instance of Jones, instances of Barnes or no
instances of Smith (yet).

I need to be able to automatically update the first workbook with the
status as indicated on the second.

Obviously vlookup and matching stops at the first instance. I do not
quite understand the ROW() and SMALL() formulae given in other
discussions.

The reason for not combining the two workbooks into one is to have a
complete population of clients separate from the sheet that staff is
working on.

Please can anyone assist? I tought myself some makro basics and would
be willing to use this (although it is not preferrable).

Kind regards

Johan

 

[Mike H]

Hi,

Try this array entered

=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$E$1),INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20=$E$1,ROW($A$1:$A$20)-ROW($E$1)+1),ROWS(B$1:B1))),"")

It Matches E1 to Column A and returns column B so is easy enough to modify
to meet your needs. Drag down to return the second and subsequent matches.

Mike