Asking Cell A1 to display a certain value if a certain value was found in Cell B1

[ameenuksg]

	Column A	Column B
1	If these texts in B1 found 'Wafer' or 'WF', return text 'Wafer', and if texts 'Doubleflanged' or 'double flange' found, return value 'double flange' and none of the earlier texts found, return 'can'f find anything' value.
2
3

 

[Cubist]

Try:

=IFERROR(IF(OR(ISNUMBER(SEARCH("Wafer",B1)),ISNUMBER(SEARCH("WF",B1))),"Wafer",
         IF(OR(ISNUMBER(SEARCH("Doubleflanged",B1)),ISNUMBER(SEARCH("double flange",B1))),"double flange",
         "can't find anything")),"can't find anything")

 

[ameenuksg]

Try:

=IFERROR(IF(OR(ISNUMBER(SEARCH("Wafer",B1)),ISNUMBER(SEARCH("WF",B1))),"Wafer",
         IF(OR(ISNUMBER(SEARCH("Doubleflanged",B1)),ISNUMBER(SEARCH("double flange",B1))),"double flange",
         "can't find anything")),"can't find anything")

Thank you so much,it worked as written but I did some changes to it to add on other conditions but ended with annoying message from excel:
Amended formula:
=IFERROR(IF(OR(ISNUMBER(SEARCH("Wafer",$L2)),ISNUMBER(SEARCH("WF",$L2))),"Wafer",
IF(OR(ISNUMBER(SEARCH("Double Flange",$L2)),ISNUMBER(SEARCH("DoubleFlange",$L2))),"Double Flange",
IF(OR(ISNUMBER(SEARCH("Mono Flange",$L2)),ISNUMBER(SEARCH("MonoFlange",$L2))),"Mono Flange",
IF(OR(ISNUMBER(SEARCH("L",$L2)),ISNUMBER(SEARCH("Lug",$L2))),"Lug",
"can't find anything"),"can't find anything")

I am getting the 'you've entered too many arguments for this function' from excel and cant get thru with the above amended formula. Could you help me with this one too?

 

[Cubist]

=IFERROR(
    IF(
        OR(ISNUMBER(SEARCH("Wafer",$L2)),ISNUMBER(SEARCH("WF",$L2))),
        "Wafer",
        IF(
            OR(ISNUMBER(SEARCH("Double Flange",$L2)),ISNUMBER(SEARCH("DoubleFlange",$L2))),
            "Double Flange",
            IF(
                OR(ISNUMBER(SEARCH("Mono Flange",$L2)),ISNUMBER(SEARCH("MonoFlange",$L2))),
                "Mono Flange",
                IF(
                    OR(ISNUMBER(SEARCH("L",$L2)),ISNUMBER(SEARCH("Lug",$L2))),
                    "Lug",
                    "can't find anything"
                )
            )
        )
    )

 

[ameenuksg]

=IFERROR(
    IF(
        OR(ISNUMBER(SEARCH("Wafer",$L2)),ISNUMBER(SEARCH("WF",$L2))),
        "Wafer",
        IF(
            OR(ISNUMBER(SEARCH("Double Flange",$L2)),ISNUMBER(SEARCH("DoubleFlange",$L2))),
            "Double Flange",
            IF(
                OR(ISNUMBER(SEARCH("Mono Flange",$L2)),ISNUMBER(SEARCH("MonoFlange",$L2))),
                "Mono Flange",
                IF(
                    OR(ISNUMBER(SEARCH("L",$L2)),ISNUMBER(SEARCH("Lug",$L2))),
                    "Lug",
                    "can't find anything"
                )
            )
        )
    )

Thank you, I did try the above but keeping getting the 'The formula is missing an opening or closing parenthesis' message from excel.

 

[Cubist]

Sorry, this should work.

=IFERROR(
    IF(
        OR(ISNUMBER(SEARCH("Wafer",$L2)),ISNUMBER(SEARCH("WF",$L2))),
        "Wafer",
        IF(
            OR(ISNUMBER(SEARCH("Double Flange",$L2)),ISNUMBER(SEARCH("DoubleFlange",$L2))),
            "Double Flange",
            IF(
                OR(ISNUMBER(SEARCH("Mono Flange",$L2)),ISNUMBER(SEARCH("MonoFlange",$L2))),
                "Mono Flange",
                IF(
                    OR(ISNUMBER(SEARCH("L",$L2)),ISNUMBER(SEARCH("Lug",$L2))),
                    "Lug",
                    "can't find anything"
                )
            )
        )
    ),
    "can't find anything"
)

 

[ameenuksg]

its not returning the right value if column L has the following words:

	Column D - Category	Column L - Description
744	=IFERROR(IF(OR(ISNUMBER(SEARCH("Wafer",$L527)),ISNUMBER(SEARCH("WF",$L527))),"Wafer", IF(OR(ISNUMBER(SEARCH("Double Flange",$L527)),ISNUMBER(SEARCH("DoubleFlange",$L527))),"Double Flange", IF(OR(ISNUMBER(SEARCH("Mono Flange",$L527)),ISNUMBER(SEARCH("MonoFlanged",$L527))),"Mono Flange", IF(OR(ISNUMBER(SEARCH("L",$L527)),ISNUMBER(SEARCH("Lug",$L527))),"Lug","can't find anything")))),"can't find anything")	1000MM PN16 NCI/BT/NBR Dbl Flg BFV - W46 Double Flange Butterfly Valve Size: DN1000 Working Pressure: 10 bar Flange Connection: PN 16 Body: Nodular Cast Iron (JS1030) Disc: Alubronze AB2 Shaft: SS 431 Liner: NBR with Gearbox
745		400MM PN10 NCI(DI)/SST/NBR LUG BFLY - Body: NCI(DI), Disc: SS316, Seat: BUNA(NBR), Gear operated
746		800MM PN10 NCI LUG BFV, BRZ, BUNA - BODY= NCI(DI), DISC = ALU-BRZ, SEAT = BUNA(NBR), GEAR OP.
747		600MM 10K NCI/BRZ/NBR DBFL BFV U-TYPE - Double Flanged Butterfly valve (U-type) JIS F-7480 with gearbox. Body: Nodular Cast Iron (GGG40), Disc: alubronze (AB2) , Seat: NBR. Max pressure: 10 bar. Flange Connection: 10K. Face to face: 170mm.
748		200MM #150 NCI/BRZ/NBR LUG BFV - ANSI 150 Lug butterfly valve. Body: Nodular Cast Iron (Ductile Iron), Disc: ALU-BRZ, Seat: NBR, Lever operated.
749		300MM PN10 LONG MONO BFV NCI/BRZ/EPDM - Monoflanged Butterfly Valve with Gearbox, Long type. Body: Nodular Cast Iron (GGG40), Disc: Alubronze (AB2). Seat: EPDM. Max working pressure: 10 bar. Flange Connection: PN10. Face to face: 114mm. Topflange: ISO5211 F10 (Ø102), Stem: 22mm Square (90°)

 

[Cubist]

Check your cell references. You're showing row 744 but you're referencing L527.