Search and Organize dynamically

[fourmurphys]

I've exhausted my knowledge (admittedly shallow) and tried things like INDEX, MATCH, and Visual Basic, but I'm at a wall... hoping/praying for some assistance...

I'm attaching the file I'm working with but here is my 'wish list':
Under "Mandatory Focus" I have two columns, "Football" and "5+". For each cell (when applicable) under each, I'd like it search the 'Focus' columns above for certain words in AD7:AD25 and input the account name if specific text is found. I've input an example to help.

Using the Acme Account as my example, I have data in two of the four possible focus cells. What I'd like is for Acme Account to show up below under "Football" and "5+", and so on for all accounts listed under 'accounts'. There will accounts who have neither, so it would need to be selective. The cells that would count as football are AD11:AD15, and for 5+ would be AD20 and AD24:25.

This may be asking too much, but I would sure appreciate any help or direction. I'm trying to make this easy for our sales people...

Thank you!!

[fourmurphys]

bump... thanks!

[martindwilson]

Under Mandatory Focus" I have two columns, "Football" and "5+".

where are these columns? and where are the accounts?
never mind found them, forgot to scroll down

but could you fill in a few more examples and show results you expect (colour highlighted would be good)

[olasa]

I am not sure what you want, but this migh be it (see encl. file).

There are 2 new formulas:
One in H8 (and I8) that finds the words your're looking for --> True/False
And one Array formula (in H65) that looks up the account name and puts it in the list. However, I hesitated to use it since it's quite complicated.

So, I included two other options:
First is an Autofilter which I think is a simpler solution.
And the other is a Pivottable. I formatted it so doesn't look like a 'normal' Pivottable. To update the Pivottable, rightclick on the table and select Refresh data.

Hope can be a start
Ola

[fourmurphys]

thanks so much! I'm not able to test it where I am, but will later today and post update. Again, thanks for the effort!

[olasa]

You wellcome and thanks for the respone.
//Ola

[fourmurphys]

Ok, so I've had a chance to tinker, and I love the array formulae!

Works like a charm!

Thanks so much!

[olasa]

Happy to hear.
Array formulas do bring a new dimension to Excel.
//Ola