Try this array formula
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.
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 by happy_smiler1; 2012-08-28 at 09:56 PM.
Try this array formula
It worked in my tests. Did you array-enter it? What did it give, what should it have given?
It just returned an error. I tried tweaking it to no avail. What I need is one lookup that will return multiple values. From the example you can see that add code 123 displays multiple code values 1, 2 and 6. I need to show the dates for add code 123 like this:
add code 1st time 2nd time 3rd time
123 1st Jan 2008 4th sept 2008 1st jan 2009
so on and so on for every code that is in the spreadsheet, I have alot of data and the example is just a snippet and i wanted to see if there was a shortcut of doing it as doping it manually will take a good few days. I have tried a pivot table but that doesnt work?
Thank you for your help, this is really appreciated.
I bet you didn't array-enter the formula.
The formula you tried above, if that works, would it not just give you the first value of the duplicate each time, rather than looking down to the next value in the duplicate and pulling the next date through?
Last edited by happy_smiler1; 2012-08-30 at 06:14 AM.
No, because you pull it across to the next cell (G2,H2) so as to get them both.