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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!