Help with IF

[tommcbrny]

Hello,

I need to capture text from cell D2 (example only) if cell D1 contains
"abc", otherwise use the text from D1. I am able to do this with the
following formula:
(IF(ISERR(FIND("abc",D1,1)),D1,D2)

I would like to expand this now so that if D1 contains either "abc", "def",
"ghi", "jkl", or ""mno", I capture the text in D2, else use the text from D1.
Is this possible? I have fiddled with the original by strining the
additional requirements on and can get Excel to accept the formula as
"correct" but not accomplish what I need to do.

Thank you,
Tom

 

[veryeavy]

I need to capture text from cell D2 (example only) if cell D1 contains

"abc", otherwise use the text from D1. I am able to do this with the
following formula:
(IF(ISERR(FIND("abc",D1,1)),D1,D2)

I would like to expand this now so that if D1 contains either "abc", "def",
"ghi", "jkl", or ""mno", I capture the text in D2, else use the text from D1.
Is this possible? I have fiddled with the original by strining the
additional requirements on and can get Excel to accept the formula as
"correct" but not accomplish what I need to do.

Can you not use something like:

if(or(D1="abc",D1="def", etc),D2,D1)

Best Regards,

Matt

 

[FiluDlidu]

If you don't want "abcd" to be a match, but "ABC" is OK, you can use the
following:

=IF(OR(D1="abc",D1="def",D1="ghi",D1="jkl",D1="mno"),D2,D1)

If you don't want "ABC" to be a match, but "abcd" is OK, you can use:

=IF(OR(1-ISERROR(FIND("abc",D1)),1-ISERROR(FIND("def",D1)),1-ISERROR(FIND("ghi",D1)),1-ISERROR(FIND("jkl",D1)),1-ISERROR(FIND("mno",D1))),D2,D1)

If you want neither "ABC" or "abcd" to be matches, you can use:

=IF(OR(1-ISERROR(FIND("abc",D1)),1-ISERROR(FIND("def",D1)),1-ISERROR(FIND("ghi",D1)),1-ISERROR(FIND("jkl",D1)),1-ISERROR(FIND("mno",D1))),IF(OR(D1="abc",D1="def",D1="ghi",D1="jkl",D1="mno"),D2,D1),D1)

If you want them both "ABC" and "abcd" to be a match, you can use:

=IF(OR(1-ISERROR(SEARCH("abc",D1)),1-ISERROR(SEARCH("def",D1)),1-ISERROR(SEARCH("ghi",D1)),1-ISERROR(SEARCH("jkl",D1)),1-ISERROR(SEARCH("mno",D1))),D2,D1)

 

[smartin]

Hello,

I need to capture text from cell D2 (example only) if cell D1 contains
"abc", otherwise use the text from D1. I am able to do this with the
following formula:
(IF(ISERR(FIND("abc",D1,1)),D1,D2)

I would like to expand this now so that if D1 contains either "abc", "def",
"ghi", "jkl", or ""mno", I capture the text in D2, else use the text from D1.
Is this possible? I have fiddled with the original by strining the
additional requirements on and can get Excel to accept the formula as
"correct" but not accomplish what I need to do.

Thank you,

Hi Tom

Put the list of "positive" values (abc, def, etc.) in some
out-of-the-way place and name this range "MyStuff".

Then use

=IF(ISNA(VLOOKUP(D1,MyStuff,1,false)),D2,D1)

 

[tommcbrny]

Thanks, this almost works, but I should have been more clear in my question.
The cell being tested will not contain exactly "abc", "def", etc, but more
along the line of "abc-123", "def-432", and so on. Is there any way to use
the list but require a match on the entire list value in question?

Thanks again.
Tom

 

[smartin]

Ah, well, that's different I suppose.

But let's make sure I understand the problem:

Suppose D1 contains "123abc456". The list allows "abc" as a "positive"
value. This should return D1, right?

This being the case, I can't think of a one-shot solution.

Something that does work involves a helper column alongside the positive
value range. I put the positive values in F and the following in G:

=FIND(F1,$D$1)

Fill this down appropriately.

In some other cell put this array* formula

=IF(MAX(IF(NOT(ISERROR(G1:G3)),G1:G3))>0,D1,D2)

Adjust ranges in G as necessary.

*Commit array formula with Ctrl+Shift+Enter, not just Enter.

 

[JBeaucaire]

Did you try my original suggestion? I can't tell if you're saying it
would work or not.

=IF(ISNUMBER(OR(FIND({"abc","def","ghi","jkl","mno "},D1,1))),D2,D1)