Forum Discussion
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/XXX...
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",
)
)
)
)
)
)
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.
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})
