# Populate a column with text (with no blank spaces) from another column based on an adjacent word in third column

I want to populate a column, with text (and No null/blank values) from a column, based on an adjacent word found in third column. Please see the image below.

I would like the `Names` in `Col A` having values :

• `Giants` to be placed under the Giant Column (Column D)
• `Bees` to be placed under the Bees Column (Column E) and so on.
• However `Names` values for Bees and Titanic should begin in the first available cell in their respective columns.

On a side note, The actual list has over 150 names and 9 different teams of uneven quantities.

So, any ideas how can I proceed with ?

• You mean to say, Kyle & Sara in Giants (Column D), Jush & Jackie in Clo E and so ahead !! – Rajesh S May 25 '18 at 11:08
• Yes please, using a formula, so that I don't have to manually move them every time a new list is made. Thank you – HomeFly May 25 '18 at 11:18
• Have you considered the use of pivot table? – Prasanna May 25 '18 at 11:25
• @HomeFly, please check the Solution I've posted. – Rajesh S May 25 '18 at 13:10

In D2 enter the array formula:

``````=IFERROR(INDEX(\$A\$1:\$A\$20,SMALL(IF(\$B\$2:\$B\$20=D\$1,ROW(\$B\$2:\$B\$20)),ROW(1:1))),"")
``````

and copy both across and downward:

Naturally replace the 20 with the real end-of-data.

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.

• It works Amazingly, it is perfect, and thank you for the heads up about replacing the "20"! I am very appreciative of this assistance. This is my second year searching forums and youtube for this solution but I realize I didn't know what search words to use, it was very frustrating, so know that I am grateful. It works perfectly!! – HomeFly May 25 '18 at 14:53

I would like to suggest this Array Formula with different approach. Write the Formula in Cell D2 & fill to Right then Down.

N.B. Finish the Formula with Ctrl+Shift+Enter.

``````{=IFERROR(INDEX(\$A\$2:\$A\$13, MATCH(0, IF(D\$1=\$B\$2:\$B\$13, COUNTIF(D\$1:D1, \$A\$2:\$A\$13), ""), 0)),"")}
``````

Check the Screen Shot:

Note, This Formula will produce the same list, even if order of Names & Teams will change.

• This is amazing, thank you so much, I have been searching for two days straight, on forums and youtube trying to find this solution! It is exactly what I was looking for. Thank you again – HomeFly May 25 '18 at 14:35
• @HomeFly, happy to help you,, if you feel you can accept this as Answer also. Keep Asking :-) – Rajesh S May 26 '18 at 6:39
• I tried to accept this one, with a tick, as well but I don't know how to do both. And the rules state not to post question or comments that aren't directly related to the topic nor thanks (which is really weird, as I am grateful for the help), but my thanks was the only way to let other viewers see that both methods worked for me. If you know how please instruct. thanks – HomeFly May 28 '18 at 13:39