happy_smiler1
New member
- Joined
- Aug 28, 2012
- Messages
- 67
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- office 365
Hi,
I am trying to get a formula that will return the date (column D) into column G using the lookup value (unique value), but as you can see, there are duplicate codes for the add code, which have different dates. I would like it so that the formula looks up the first lookup up code, then pulls through the date for the first code, then if there is another add code but a different code for it to pull that date through but have it in column G (1st) and H (2nd). In essence, I would like to see how many times the add code appears and if its more than once, to place it along in columns G and H.
I have tried lookups, Index, Index with rows, 2 way lookups, I just cant seem to get it to work so that it looks down the rows and pulls through the correct dates for the add code and if there are duplicates to place them side by side.
As you can see Add code 123 has code 1,2 and 6.
=index($D$1:$D$6,small(if($B$1:$B$6=$F$1,if($A$1:$ A$6=$E$1:$E$5,row($D$1:$D$6)-row($D$1)-1))))
Any help would be greatly appreciated.
Many thanks in advanced.
I am trying to get a formula that will return the date (column D) into column G using the lookup value (unique value), but as you can see, there are duplicate codes for the add code, which have different dates. I would like it so that the formula looks up the first lookup up code, then pulls through the date for the first code, then if there is another add code but a different code for it to pull that date through but have it in column G (1st) and H (2nd). In essence, I would like to see how many times the add code appears and if its more than once, to place it along in columns G and H.
I have tried lookups, Index, Index with rows, 2 way lookups, I just cant seem to get it to work so that it looks down the rows and pulls through the correct dates for the add code and if there are duplicates to place them side by side.
As you can see Add code 123 has code 1,2 and 6.
=index($D$1:$D$6,small(if($B$1:$B$6=$F$1,if($A$1:$ A$6=$E$1:$E$5,row($D$1:$D$6)-row($D$1)-1))))
Code | Add Code | Name | Date | Lookup Value | 1st Time | 2nd Time | ||
Col 1 | 1 | 123 | Jordan | 1st Jan 2008 | 123 | |||
Col 2 | 4 | 234 | James | 2nd April 2008 | 234 | |||
Col 3 | 3 | 456 | Luke | 3rd Jan 2008 | 456 | |||
Col 4 | 2 | 123 | Sarah | 4th Sept 2008 | 789 | |||
Col 5 | 5 | 789 | Mike | 5th Mar 2008 | 987 | |||
Col 6 | 6 | 123 | Vanessa | 1st Jan 2009 |
Any help would be greatly appreciated.
Many thanks in advanced.
Last edited: