# Return header if word within cell matches word within any array

Closed
DonJuan - Nov 10, 2015 at 11:27 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Nov 10, 2015 at 02:23 PM
Hello guys,

I'm trying to classify a cell [G1] based on particular words found within 2 columns [D1 & E1]. This classification copies down from [G1] down to [G20]

Basically, I've got 10 different classification options. If a particular word within [D1 & E1] matches a word in an array in my classification tab, the result in cell G1 should be the header of the classification tab [Option X].

Option 1 Option 2 [Option 10]
Henk Aap ..
Bert Noot ..
Fred
Test

For example: If D1 is "Henk eats a carrot", cell G1 should be "Option 1".

At the moment if have a formula which is working:

"IF(MAX(IFERROR(SEARCH(\$A\$5:\$A\$8,D1),0))>0,\$A\$4"

But all together it's quite a horrible formula:

=IF(MAX(IFERROR(SEARCH(\$A\$5:\$A\$8,D1),0))>0,\$A\$4,IF(MAX(IFERROR(SEARCH(\$B\$5:\$B\$8,D1),0))>0,\$B\$4, IF(MAX(IFERROR(SEARCH(\$C\$5:\$C\$8,D1),0))>0,\$C\$4,

ETC. ETC.

- Ideally it would be a dynamic formula ignoring blanks. So someone can add/remove any classifications in the classification columns
- In some occasions a classification only works when 2 conditions are satisfied, for example: "Henk eats a carrot with Aap", meaning the following line is added in the total formula:

=IF(AND(MAX(IFERROR(SEARCH(\$A\$5:\$A\$8,D1),0))>0,MAX(IFERROR(SEARCH(\$B\$5:\$B\$8,D1),0))>0,\$A\$4,

Do you guys have any suggestions/tips for a more efficient formula?

Many thanks!

Related:

## 1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Nov 10, 2015 at 02:23 PM
I would confess that I have followed all what you explained, but would not VBA would be more efficient and cleaner approach