Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with IF
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with IF
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with IF
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",D 1="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) "tommcbrny" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with IF
tommcbrny wrote:
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) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with IF
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" wrote: tommcbrny wrote: 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) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with IF
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. tommcbrny wrote: 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" wrote: tommcbrny wrote: 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) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with IF
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) -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=57649 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|