Highlighted
New Contributor

# IFS with nested FIND formulas

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.

regards, Paul

6 Replies
Highlighted

# Re: IFS with nested FIND formulas

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",                    )                )            )        )    ))`
Highlighted

# Re: IFS with nested FIND formulas

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)

Highlighted

# Re: IFS with nested FIND formulas

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

Highlighted

# Re: IFS with nested FIND formulas

@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.

Highlighted

# Re: IFS with nested FIND formulas

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})

Highlighted

@phitchman66

My pleasure!