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.

Excel image

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:


and copy both across and downward:

enter image description here

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:

enter image description here 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.