Lookup product based on multiple criteria in 1 column

[qontrol]

Hi all,

I've got a file where I have multiple products and they have certain characteristics. These characteristics also have values.

I need to lookup which products only have the characteristic value of "rain" or "rain" AND "fire". If a product does not meet this criteria, the lookup should return "criteria not met".

I have no idea how to start this.

Thanks in advance!

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Characteristic[/TD]
[TD]Characteristic Value[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Property[/TD]
[TD]Rain[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Property[/TD]
[TD]Fire[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Property[/TD]
[TD]Rain[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Property[/TD]
[TD]Fire[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Property[/TD]
[TD]Wind[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Property[/TD]
[TD]Rain[/TD]
[/TR]
</tbody>[/TABLE]

 

[DataBlake]

you could do this in cell D2 and copy it down to your last row

=if(isnumber(search(or(and("rain", "fire"), "rain"), C2)), A2, "criteria not met")

i did not test this because im currently running a script that makes excel unresponsive, but let me know if this worked for you.