formulas for multiple values with duplicates

happy_smiler1

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

 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.

Last edited:

Bob Phillips

Super Moderator
Staff member
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
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
It worked in my tests. Did you array-enter it? What did it give, what should it have given?

happy_smiler1

New member
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
I bet you didn't array-enter the formula.

happy_smiler1

New member
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
No, because you pull it across to the next cell (G2,H2) so as to get them both.

happy_smiler1

New member
No, because you pull it across to the next cell (G2,H2) so as to get them both.

Thank you sooooooo much, you are the man!!!!!!!!!!!!!!!!