Microsoft Office excel 2007

Hello all, 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.

Original post:

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.Portfolio Accounts SMITHA 1111 SMITHA 1112 SMITHA 1113 JONESA 1115 JONESB 1120On 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.

SMITHA 1111 1112 1113Using 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!

Thank you so much Mike, I would have never figured that out on my own. I also didn't know exactly what to call my problem either, but it's a "Look up one value and return multiple corresponding values" problem that requires an array formula to solve. I'll edit my subject (if I can) to help others in the future.

Also, once you have a formula pasted into a cell, you need to hit CTRL+SHIFT+ENTER in order for the array formula to work. Took me a couple minutes to figure that out.

Check out the link that Mike posted for a step by step guide by Ashish Mathur.

With some careful editing of the formula from Ashish, I am able to pull exactly what I was looking for.

Thanks again Mike!

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History