EDIT: This is a "Look up one value and return multiple corresponding values" problem that requires an array formula. See Mike's post below for the array formula that worked for me.
I need help with a formula that will display multiple accounts linked to a single client portfolio on Sheet1, using the client portfolio code as the reference. The exported data lives on Sheet2, and has the format below. There are hundreds of Portfolios and multiple Accounts per Portfolio.
On Sheet1, I need to be able to list a client portfolio (SMITHA for example), and have all the accounts related to SMITHA show up in separate rows under that portfolio.
Using Vlookup only grabs the first account number, which is a problem as some client portfolios have as many as 9 accounts associated with it.
What code can I use on Sheet1 that will look in Sheet2 (the data sheet) at Column A for all rows with a certain client portfolio (SMITHA), and then return all related accounts in Column B on Sheet1?
Or is there code I can use on Sheet2 (the data sheet) that will look for all similar client portfolios, and then list the associated account numbers in new columns, so I can then use Vlookup on Sheet1 and have it pull a new column_index_num for each account there is?
Thank you very much!