IFS with nested FIND formulas

[phitchman66]

New here so forgive me.

I'm trying to use a formula to look for a text within a cell. Typically the cell will have 

XX/6A/XXXX -or-

XX/10A/XXXX -or-

XX/16A/XXXX -or-

XX/20A/XXXX -or-

XX/32A/XXXX -or-

XX/64A/XXXX -or-

=IFS(FIND("10A", A1)>0, 1.37, FIND("16A", A1)>0, 0.96 and so on

now this works IF cell A1 contains the second [logical_test] FIND (IFS=first logical test) so if the cell contains 10A it returns 1.37 BUT if it contains any of the other permutations it results in #VALUE!

I'm a bit confused by the problem.

PLEASE HELP

regards, Paul

[#2]

Hello @phitchman66,

That could be something like:

=IF(
    NOT(
        ISERR(
            FIND(
                "64A",
                A3
            )
        )
    ),
    "INSERT VALUE",
     IF(
        NOT(
            ISERR(
                FIND(
                    "32A",
                    A3
                )
            )
        ),
        "INSERT VALUE",
         IF(
            NOT(
                ISERR(
                    FIND(
                        "20A",
                        A3
                    )
                )
            ),
            "INSERT VALUE",
             IF(
                NOT(
                    ISERR(
                        FIND(
                            "16A",
                            A3
                        )
                    )
                ),
                0.96,
                 IF(
                    NOT(
                        ISERR(
                            FIND(
                                "10A",
                                A3
                            )
                        )
                    ),
                    "1.37",
                     IF(
                        NOT(
                            ISERR(
                                FIND(
                                    "6A",
                                    A3
                                )
                            )
                        ),
                        "INSERT VALUE",

                    )
                )
            )
        )
    )
)

[#3]

@phitchman66 

I'd create a little lookup table with 6A, 10A etc. in the first column and 1.37, 0.96 etc. in the second column. In the screenshot below it is in F1:G6.

With the values to search in A1 and down, you can use the following formula in B1, then fill down:

=VLOOKUP(MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1),$F$1:$G$6,2,FALSE)

 

[#4]

@PReaganthanks, I'll give it a try...

[#5]

@PReaganthanks, this worked a treat. I couldn't (well didn't want to add more data to an already congested data sheet) so I went with this.

[#6]

As a possible alternative - I included the forward slashes / to differentiate between 6A and 16A. Also, I just used 1,2,3,4 for the missing numbers to return - change those to what you're actually using.

=SUMPRODUCT(--(ISNUMBER(FIND({"/6A/","/10A/","/16A/","/20A/","/32A/","/64A/"},A1))),{1.37,1,0.96,2,3,4})

[#7]

@phitchman66

My pleasure!