Looking up multiple values based on a single criterion

Hello,

I am trying to create a formula that will pull ALL of the values from a table into another table according to a single criterion.

A sample is enclosed. I would like to match the values in Col. G with those in Col. B and, if they match, return the values (stock symbols) from Col. D and put them in Col. H, I , J, K and L. (I am assuming there would not be more than five stock symbols in a given sector (the "sectors" are coded in Col. B and the corresponding sector name is shown in Col. C)).

What I would like to end up with is the table in Cols. G-L populated with the stock symbols which I've entered manually just to illustrate what I'm trying to accomplish. I've tried to dredge up all the index, match, and lookup formulas that I USED to know eons ago, but I'm getting nowhere.

So if you experts out there can help me out, I would be most appreciative.

Many thanks in advance.

Put this formula in F3:

=IF(B3="","-",B3&"_"&COUNTIF(B$3:B3,B3))

and copy it down beyond your data in order to cope with future expansion - the hyphens will indicate how far you have copied it.

Then you can put this formula in H3:

=IFERROR(INDEX($A:$A,MATCH($G3&"_"&COLUMNS($H:H),$F:$F,0)),"")

and copy it across into I3:L3. Then copy H3:L3 down to row 27.

Hope this helps.

Pete

Excellent. Thanks so much, Pete. I tried it and it works perfectly.

Have a great 2014!

Bill

Yes, Happy New Year to you, Bill.

You might need a reminder - if that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete