Use VLOOKUP table for reverse wildcard--find value contained within target

[ozstrik3r69]

Dears good afternoon
I would like to request your help regarding this formula.
I have on column I various type of fruit and on column S various different weight.

I am looking for a formula to write in case the result if true, the word "Check". If not "Do not check" in column "AB"

The criterias that I have are the following one :

• If I contains banana and the weight is under or egal 16 then Check
• If I contains banana and the weight is under or egal 16 then Check
• If I contains orange and the weight is under or egal 22 then Check
• If I contains mango and the weight is under or egal 22 then Check
• If I contains coconut and the weight is under or egal 22 then Check
• If I contains lemon and the weight is under or egal 22 then Check
• If I contains grape and the weight is under or egal 11 then Check
• If I contains papaya and the weight is under or egal 17 then Check
  
  The 2 lasts are
• If I contains "Fresh watermelons" or "Melons (including watermelons)" and weight is under or egal 21 then check
• If I contains "Fresh melons (excl. watermelon" or "Peel of citrus fruit or melons" and weight is under or egal 16 then check
  

I tried several formulas but nothing was correct.
if someone can help me, I will be grateful

Regards

[6StringJazzer]

You need a table of fruits and maximum weights, then the formula becomes quite simple. You look up the maximum weight with VLOOKUP and compare that to the weight in column S. See attached for example. Table in columns A:B, data starts in column I.

[ozstrik3r69]

6StringJazzer
thanks for the feedback.

What is now missing, is, words like bananas mango coconut and so on, are part of contents in cell.
What I will need, is to find a way in my vlookup to use the criteria "contain the word or part of the word"

Example, in cell "I" i have "banana with sugar" or "Fresh or dried guavas, mangoes"
Vllokup will have to find the word banana or mango to compare the weight

Do you think it愀 possible ?

Regards

[6StringJazzer]

There may be a way to do this but I don't know what it is. I am changing your title to reflect your actual question.

[Bo_Ry]

Please try at AB2

=IF(OR(AND(S2<=11,COUNT(SEARCH({"grape"},I2))),AND(S2<=16,COUNT(SEARCH({"banana";"Fresh melons"},I2))),AND(S2<=21,COUNT(SEARCH({"watermelons";"melons"},I2))),AND(S2<=22,COUNT(SEARCH({"orange";"mango";"coconut";"lemon"},I2)))),"Check","Do not check")

[ozstrik3r69]

Works like charm !!!!
Thank a lot !