Match offset only if duplicated

[Alex]

This is my issue:

ID Promotion Date Pay Grade
1 01/10/06 5
1 02/01/08 7
2 05/09/05 4
3 10/12/08 2
4 01/03/07 3
4 10/11/08 5
5 01/01/08 2

In another sheet I have

ID Pay Grade Before Promotion Pay Grade After
Promotion

1
2
3
4
5
How can I create a formula that will find a match and if the match
have a duplicate copy the pay grade on the pay grade before promotion
and then another formula to add the new pay grade after promotion.


Thanks for you help.

 

[T. Valko]

Based on your sample data being sorted by ID...

Table data in the range A2:C8

E2:E6 = 1,2,3,4,5

Enter this formula in F2:

=VLOOKUP(E2,A$2:C$8,3,0)

Enter this formula in G2:

=IF(COUNTIF(A$2:A$8,E2)>1,LOOKUP(2,1/(A$2:A$8=E2),C$2:C$8),"")

Select both F2 and G2 and copy down to F6:G6

 

[Alex]

Valko,
You are the man!!! thank you.

Based on your sample data being sorted by ID...

Table data in the range A2:C8

E2:E6 = 1,2,3,4,5

Enter this formula in F2:

=VLOOKUP(E2,A$2:C$8,3,0)

Enter this formula in G2:

=IF(COUNTIF(A$2:A$8,E2)>1,LOOKUP(2,1/(A$2:A$8=E2),C$2:C$8),"")

Select both F2 and G2 and copy down to F6:G6

--
Biff
Microsoft Excel MVP











- Show quoted text -

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Valko,
You are the man!!! thank you.