if count and seach criteria. (find part of value en a cells and return with a certain value)

[muggi]

Hi,
i have below formula, that search a certain cell (c8), and if found any of the search criteria , it should return a value.

=IF(COUNT(SEARCH({"harald";"fakta";"coop";"bilka"};C8));"m";IF(ISNUMBER(SEARCH({"stof";"babysam"};C8));"d";IF(ISNUMBER(SEARCH({"læge";"fysioterapeut"};C8));"L";"")))

But is it possible to change this:

({"harald";"fakta";"coop";"bilka"} and also the 2 other criteria

to a range of cells (each cell with 1 criteria) containing the search criteria, that way it would be easier to maintain the data.
BUT the formula should still be able to seach the C8 and look if part of the value in C8 contains the criteria?

Is it possible ?

 

[XOR LX]

Hi,

Sure. With e.g. B1:B4 containing "harald","fakta","coop","bilka", C1:C2 containing "stof","babysam" and D1:D2 containing "læge","fysioterapeut":

=IF(COUNT(LOOKUP(32^19,SEARCH(B1:B4,C8))),"m",IF(COUNT(LOOKUP(32^19,SEARCH(C1:C2,C8))),"d",IF(COUNT(LOOKUP(32^19,SEARCH(D1:D2,C8))),"L","")))

Regards

 

[muggi]

Thank you very much for the help and fast reply. Always people in here taking their time to help others

I don't understand what the 32^19 does, but hey it works

 

[XOR LX]

You're welcome!

(It's just an arbitrarily, though also sufficiently, large number! )