Match offset only if duplicated
 

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.

Match offset only if duplicated
 

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


"Alex" wrote in message
...
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.

Match offset only if duplicated
 

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


On Oct 21, 9:17*pm, "T. Valko" wrote:
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

"Alex" wrote in message

...



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.- Hide quoted text -

- Show quoted text -

Match offset only if duplicated
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Alex" wrote in message
...
Valko,
You are the man!!! thank you.


On Oct 21, 9:17 pm, "T. Valko" wrote:
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

"Alex" wrote in message

...



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.- Hide quoted text -

- Show quoted text -