Text String search with Min value slab vendor base

[anilgopi99i]

Hi,
I have a simulation:

Col.A Col.B Col.C Col.D
Vendor Actual Wt Volume Wt Result
1. ABC 15 25 30
2. ABC 25 45 45
3. XYZ 8 9 10
4. XYZ 15 8 15

Where Vendor ABC minimum weight slab is 30 Kgs.
Vendors other than ABC minimum weight slab is 10 Kgs.
Vendor ABC, any value in Col. B & C less than 30 Kgs., result should be 30 Kgs.
Vendor ABC, any value in Col. B & C is greater than 30 Kgs, result should be higher value of any cell in B or C
Same logic for rest of vendors but with weight slab of minimum 10 kgs.

Would appreciate your resolution.

[sweep]

Hi,

In row 2,

Please Login or Register  to view this content.

and copied down gives the result that you specify.

[anilgopi99i]

Formula working fine, thanks for your quick resolution.

[anilgopi99i]

Thanks but one query. I want to have all options in the formula.
If Cell A2 contain TEXT ABC & ABC weight slab is 30 kgs, if Cell B2 or C2 is less than ABC weight slab 30, then result in D2 should be 30.
If Cell A2 contain TEXT ABC & ABC weight slab 30 kgs, if Cell B2 or C2 is Greater than ABC weight slab 30, then result in D2 should be highest value of Cell B2 or C2.
If Cell A2 does not contain TEXT ABC then weight slab will be 10 kgs and if Cell B2 or C2 is less than Non ABC weight slab of 10, then result in D2 should be 10.
If Cell A2 does not contain TEXT ABC then Non ABC weight slab 10 kgs, if Cell B2 or C2 is Greater than Non ABC weight slab 10, then result in D2 should be highest value of Cell B2 or C2.

[anilgopi99i]

I have developed a formula =(IF(ISNUMBER(SEARCH("ABC",A2)),MAX(B2,C2,30),MAX(B2,C2,10))) and is working fine. Thanks for the basic idea.