Return header if word within cell matches word within any array

Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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!

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I would confess that I have followed all what you explained, but would not VBA would be more efficient and cleaner approach
0