Match offset only if duplicated

  • Thread starter Thread starter Alex
  • Start date Start date
A

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.
 
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
 
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 -
Click to expand...
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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