formulas for multiple values with duplicates

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))))


CodeAdd CodeNameDateLookup Value1st Time2nd Time
Col 11123Jordan1st Jan 2008123
Col 24234James2nd April 2008234
Col 33456Luke3rd Jan 2008456
Col 42123Sarah4th Sept 2008789
Col 55789Mike5th Mar 2008987
Col 66123Vanessa1st Jan 2009

Any help would be greatly appreciated.

Many thanks in advanced.
 
Last edited:

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,941
Reaction score
0
Points
36
Excel Version(s)
O365
Try this array formula

=IFERROR(INDEX($D$1:$D$7,SMALL(IF($B$1:$B$7=$F2,ROW($B$1:$B$7)),COLUMN((A1)))),"")
 

happy_smiler1

New member
Joined
Aug 28, 2012
Messages
67
Reaction score
0
Points
0
Excel Version(s)
office 365
Try this array formula

=IFERROR(INDEX($D$1:$D$7,SMALL(IF($B$1:$B$7=$F2,ROW($B$1:$B$7)),COLUMN((A1)))),"")


Hi,

Many thanks for the reply, i have tried this, but it doesnt return the value(s) that i need as it doesnt lookup the correct cells, back to the drawing board i guess.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,941
Reaction score
0
Points
36
Excel Version(s)
O365
It worked in my tests. Did you array-enter it? What did it give, what should it have given?
 

happy_smiler1

New member
Joined
Aug 28, 2012
Messages
67
Reaction score
0
Points
0
Excel Version(s)
office 365
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?

Any ideas?

Thank you for your help, this is really appreciated.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,941
Reaction score
0
Points
36
Excel Version(s)
O365
I bet you didn't array-enter the formula.
 

happy_smiler1

New member
Joined
Aug 28, 2012
Messages
67
Reaction score
0
Points
0
Excel Version(s)
office 365
I bet you didn't array-enter the formula.

By you meaning ctrl+shift+enter, tried it, got an error #name.

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?

Thanks,
 
Last edited:

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,941
Reaction score
0
Points
36
Excel Version(s)
O365
No, because you pull it across to the next cell (G2,H2) so as to get them both.
 
Top