Match offset only if duplicated

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.
 
T

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
 
A

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

T. Valko

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top