Lookup Multiple Results and omit Duplicates

Hi Everyone,

I'm trying to insert formula that works like a vlookup but it will return multiple results and omit duplicates.
Basically I have a whole list of accounts belong to different owners. In the excel file it will be displayed as follows:

Account Owner Account Transaction
ABC XYZ 10
ABC XYZ -1
ABC XYZ 3
ABC IJK 9
DEF WXY 5
DEF WXY -6
DEF LMN 4
GHI XXX 2
GHI YYY 1
GHI ZZZ 7

The original file has up to 10,000+ records and I have this formula from another thread (http://www.excelforum.com/excel-form...uplicates.html) that I'm not able to modify to fit the table.

=INDEX(B$2:B$15,SMALL(IF(N(FREQUENCY(IF(A$2:A$15=E$2,MATCH(B$2:B$15,B$2:B$15,0)),MATCH(B$2:B$15,B$2:B$15,0))>0)>0,ROW(B$2:B$15)-ROW(B$2)+1),ROWS($1:1)))

Is there anyone that can explain how does this formula goes so that I can modify it. I'll really like to understand this formula, how it is derived and how it works. I've been trying to "break" it for a few days without any progress made.

Thank you in advance !

It looks like an array formula. It needs to be confirmed with Ctrl+Shift+Enter (double click on the cell with the formula then hold down the Ctrl and Shift buttons when you hit Enter).

Hi there,

Thanks for the information, I've used it as an array formula and it works well on the example given. However, I faced problem when I tried to extend the formula to cover all of my data by changing all of the A$15 to A$600 and B$15 to B$600 in the formula.

If you have changed all instances of the ranges to row 600 there is no reason why it shouldn't work.