| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If formula and text search
Normally you set up as A1 = 1, 'value if true', 'value if false'.
Can you set one up such that A1= "Text" if A1 contains "Text is a value" I've tried entering A1="Text*" I thought maybe a wildcard, but how can I get my formula to return the true value withough altering my A1 cell that says "Text is a value". Any help would be appreciated. Jason |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If formula and text search
*Maybe* this:
=IF(LEFT(A1,4)="text",value_if_true,value_if_false ) You can't directly use wildcards with IF. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Normally you set up as A1 = 1, 'value if true', 'value if false'. Can you set one up such that A1= "Text" if A1 contains "Text is a value" I've tried entering A1="Text*" I thought maybe a wildcard, but how can I get my formula to return the true value withough altering my A1 cell that says "Text is a value". Any help would be appreciated. Jason |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If formula and text search
Or, if the text may be somewhere in the middle of a sentence,
=if(iserror(FIND("TEXT",A1)),value if text not found, value if text found) "T. Valko" wrote: *Maybe* this: =IF(LEFT(A1,4)="text",value_if_true,value_if_false ) You can't directly use wildcards with IF. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Normally you set up as A1 = 1, 'value if true', 'value if false'. Can you set one up such that A1= "Text" if A1 contains "Text is a value" I've tried entering A1="Text*" I thought maybe a wildcard, but how can I get my formula to return the true value withough altering my A1 cell that says "Text is a value". Any help would be appreciated. Jason |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If formula and text search
Thanks for the info, that worked. Also, what if I want to find multiple
words, example "TEXT" and TEXT2". How can I do that? "Sean Timmons" wrote: Or, if the text may be somewhere in the middle of a sentence, =if(iserror(FIND("TEXT",A1)),value if text not found, value if text found) "T. Valko" wrote: *Maybe* this: =IF(LEFT(A1,4)="text",value_if_true,value_if_false ) You can't directly use wildcards with IF. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Normally you set up as A1 = 1, 'value if true', 'value if false'. Can you set one up such that A1= "Text" if A1 contains "Text is a value" I've tried entering A1="Text*" I thought maybe a wildcard, but how can I get my formula to return the true value withough altering my A1 cell that says "Text is a value". Any help would be appreciated. Jason |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If formula and text search
Is the text you're looking for at random spots of the string, like this:
this is TEXT some TEXT2 here Or, is the text you're looking for at the beginning of the string, like this: TEXT is here TEXT2 is also here How abount posting some *REAL* examples of the strings *and* the text you're looking for? -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thanks for the info, that worked. Also, what if I want to find multiple words, example "TEXT" and TEXT2". How can I do that? "Sean Timmons" wrote: Or, if the text may be somewhere in the middle of a sentence, =if(iserror(FIND("TEXT",A1)),value if text not found, value if text found) "T. Valko" wrote: *Maybe* this: =IF(LEFT(A1,4)="text",value_if_true,value_if_false ) You can't directly use wildcards with IF. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Normally you set up as A1 = 1, 'value if true', 'value if false'. Can you set one up such that A1= "Text" if A1 contains "Text is a value" I've tried entering A1="Text*" I thought maybe a wildcard, but how can I get my formula to return the true value withough altering my A1 cell that says "Text is a value". Any help would be appreciated. Jason |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If formula and text search
Depends on if you want to find where both are in the cell or only need 1.
If you need both: =if(iserror(AND(FIND("TEXT",A1),FIND("TEXT2",A1))) ,value if text not found, value if text found) if either/or: =if(iserror(OR(FIND("TEXT",A1),FIND("TEXT2",A1))), value if text not found, value if text found) "T. Valko" wrote: Is the text you're looking for at random spots of the string, like this: this is TEXT some TEXT2 here Or, is the text you're looking for at the beginning of the string, like this: TEXT is here TEXT2 is also here How abount posting some *REAL* examples of the strings *and* the text you're looking for? -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thanks for the info, that worked. Also, what if I want to find multiple words, example "TEXT" and TEXT2". How can I do that? "Sean Timmons" wrote: Or, if the text may be somewhere in the middle of a sentence, =if(iserror(FIND("TEXT",A1)),value if text not found, value if text found) "T. Valko" wrote: *Maybe* this: =IF(LEFT(A1,4)="text",value_if_true,value_if_false ) You can't directly use wildcards with IF. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Normally you set up as A1 = 1, 'value if true', 'value if false'. Can you set one up such that A1= "Text" if A1 contains "Text is a value" I've tried entering A1="Text*" I thought maybe a wildcard, but how can I get my formula to return the true value withough altering my A1 cell that says "Text is a value". Any help would be appreciated. Jason |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If formula and text search
if either/or:
=if(iserror(OR(FIND("TEXT",A1),FIND("TEXT2",A1))), value if text not found, value if text found) That won't work. Replace "TEXT" with TEST: =IF(ISERROR(OR(FIND("TEST",A1),FIND("TEXT2",A1))), "not found","found") A1 = This is a TEST A2 = This is TEXT2 Also, FIND is case sensitive, Test will not match TEST. Better to use SEARCH unless you specifically want to make the condition case sensitive. =IF(COUNT(1/SEARCH({"test","text2"},A1)),"found","not found") Another thing to take into consideration is "false positives" : A1 = This is a contest =IF(COUNT(1/SEARCH({"test","text2"},A1)),"found","not found") Will return found -- Biff Microsoft Excel MVP "Sean Timmons" wrote in message ... Depends on if you want to find where both are in the cell or only need 1. If you need both: =if(iserror(AND(FIND("TEXT",A1),FIND("TEXT2",A1))) ,value if text not found, value if text found) if either/or: =if(iserror(OR(FIND("TEXT",A1),FIND("TEXT2",A1))), value if text not found, value if text found) "T. Valko" wrote: Is the text you're looking for at random spots of the string, like this: this is TEXT some TEXT2 here Or, is the text you're looking for at the beginning of the string, like this: TEXT is here TEXT2 is also here How abount posting some *REAL* examples of the strings *and* the text you're looking for? -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thanks for the info, that worked. Also, what if I want to find multiple words, example "TEXT" and TEXT2". How can I do that? "Sean Timmons" wrote: Or, if the text may be somewhere in the middle of a sentence, =if(iserror(FIND("TEXT",A1)),value if text not found, value if text found) "T. Valko" wrote: *Maybe* this: =IF(LEFT(A1,4)="text",value_if_true,value_if_false ) You can't directly use wildcards with IF. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Normally you set up as A1 = 1, 'value if true', 'value if false'. Can you set one up such that A1= "Text" if A1 contains "Text is a value" I've tried entering A1="Text*" I thought maybe a wildcard, but how can I get my formula to return the true value withough altering my A1 cell that says "Text is a value". Any help would be appreciated. Jason |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If formula and text search
Thanks, I think I now have some sort of order issue here...
=IF(AND(ISERROR(OR(SEARCH("Director",VLOOKUP(G10,'[Active employees 3.2.08.xls]data'!$A:$N,14,FALSE),1),ISERROR(SEARCH("Manager", VLOOKUP(G10,'[Active employees 3.2.08.xls]data'!$A:$N,14,FALSE),1)))),J10=5000),VLOOKUP(N10, '[Active employees 3.2.08.xls]Approvers'!$A:$O,2,FALSE),IF(J10=5000,G10,0)) What I want to do is, lookup ("Director" or "Manager") in a job title field. If either of those are TRUE AND J10 = 5000, return approver "C". If both are false, enter approver "A". I keep getting approver "A" even though the Job title has neither "Director" or "Manager". Any adise would be appreciated. Jason "T. Valko" wrote: if either/or: =if(iserror(OR(FIND("TEXT",A1),FIND("TEXT2",A1))), value if text not found, value if text found) That won't work. Replace "TEXT" with TEST: =IF(ISERROR(OR(FIND("TEST",A1),FIND("TEXT2",A1))), "not found","found") A1 = This is a TEST A2 = This is TEXT2 Also, FIND is case sensitive, Test will not match TEST. Better to use SEARCH unless you specifically want to make the condition case sensitive. =IF(COUNT(1/SEARCH({"test","text2"},A1)),"found","not found") Another thing to take into consideration is "false positives" : A1 = This is a contest =IF(COUNT(1/SEARCH({"test","text2"},A1)),"found","not found") Will return found -- Biff Microsoft Excel MVP "Sean Timmons" wrote in message ... Depends on if you want to find where both are in the cell or only need 1. If you need both: =if(iserror(AND(FIND("TEXT",A1),FIND("TEXT2",A1))) ,value if text not found, value if text found) if either/or: =if(iserror(OR(FIND("TEXT",A1),FIND("TEXT2",A1))), value if text not found, value if text found) "T. Valko" wrote: Is the text you're looking for at random spots of the string, like this: this is TEXT some TEXT2 here Or, is the text you're looking for at the beginning of the string, like this: TEXT is here TEXT2 is also here How abount posting some *REAL* examples of the strings *and* the text you're looking for? -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thanks for the info, that worked. Also, what if I want to find multiple words, example "TEXT" and TEXT2". How can I do that? "Sean Timmons" wrote: Or, if the text may be somewhere in the middle of a sentence, =if(iserror(FIND("TEXT",A1)),value if text not found, value if text found) "T. Valko" wrote: *Maybe* this: =IF(LEFT(A1,4)="text",value_if_true,value_if_false ) You can't directly use wildcards with IF. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Normally you set up as A1 = 1, 'value if true', 'value if false'. Can you set one up such that A1= "Text" if A1 contains "Text is a value" I've tried entering A1="Text*" I thought maybe a wildcard, but how can I get my formula to return the true value withough altering my A1 cell that says "Text is a value". Any help would be appreciated. Jason |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If formula and text search
So, you want to lookup G10 and if either Director or Manager is within that
string *and* if J10=5000 then do this lookup: VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,FALSE) If Director or Manager is not found *but* J10=5000 then return G10, otherwise return 0. Ok, try this... I'm assuming all of lookup values *do exist* : =IF(AND(COUNT(1/SEARCH({"director","manager"}, VLOOKUP(G10,'[Active employees 3.2.08.xls] Data'!A:N,14,0))),J10=5000), VLOOKUP(N10,'[Active employees 3.2.08.xls] Approvers'!A:O,2,0),IF(J10=5000,G10,0)) -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thanks, I think I now have some sort of order issue here... =IF(AND(ISERROR(OR(SEARCH("Director",VLOOKUP(G10,'[Active employees 3.2.08.xls]data'!$A:$N,14,FALSE),1),ISERROR(SEARCH("Manager", VLOOKUP(G10,'[Active employees 3.2.08.xls]data'!$A:$N,14,FALSE),1)))),J10=5000),VLOOKUP(N10, '[Active employees 3.2.08.xls]Approvers'!$A:$O,2,FALSE),IF(J10=5000,G10,0)) What I want to do is, lookup ("Director" or "Manager") in a job title field. If either of those are TRUE AND J10 = 5000, return approver "C". If both are false, enter approver "A". I keep getting approver "A" even though the Job title has neither "Director" or "Manager". Any adise would be appreciated. Jason "T. Valko" wrote: if either/or: =if(iserror(OR(FIND("TEXT",A1),FIND("TEXT2",A1))), value if text not found, value if text found) That won't work. Replace "TEXT" with TEST: =IF(ISERROR(OR(FIND("TEST",A1),FIND("TEXT2",A1))), "not found","found") A1 = This is a TEST A2 = This is TEXT2 Also, FIND is case sensitive, Test will not match TEST. Better to use SEARCH unless you specifically want to make the condition case sensitive. =IF(COUNT(1/SEARCH({"test","text2"},A1)),"found","not found") Another thing to take into consideration is "false positives" : A1 = This is a contest =IF(COUNT(1/SEARCH({"test","text2"},A1)),"found","not found") Will return found -- Biff Microsoft Excel MVP "Sean Timmons" wrote in message ... Depends on if you want to find where both are in the cell or only need 1. If you need both: =if(iserror(AND(FIND("TEXT",A1),FIND("TEXT2",A1))) ,value if text not found, value if text found) if either/or: =if(iserror(OR(FIND("TEXT",A1),FIND("TEXT2",A1))), value if text not found, value if text found) "T. Valko" wrote: Is the text you're looking for at random spots of the string, like this: this is TEXT some TEXT2 here Or, is the text you're looking for at the beginning of the string, like this: TEXT is here TEXT2 is also here How abount posting some *REAL* examples of the strings *and* the text you're looking for? -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thanks for the info, that worked. Also, what if I want to find multiple words, example "TEXT" and TEXT2". How can I do that? "Sean Timmons" wrote: Or, if the text may be somewhere in the middle of a sentence, =if(iserror(FIND("TEXT",A1)),value if text not found, value if text found) "T. Valko" wrote: *Maybe* this: =IF(LEFT(A1,4)="text",value_if_true,value_if_false ) You can't directly use wildcards with IF. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Normally you set up as A1 = 1, 'value if true', 'value if false'. Can you set one up such that A1= "Text" if A1 contains "Text is a value" I've tried entering A1="Text*" I thought maybe a wildcard, but how can I get my formula to return the true value withough altering my A1 cell that says "Text is a value". Any help would be appreciated. Jason |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If formula and text search
Thank you very much, it seems to have worked. A couple of followup questions
though. What does the count(1/search({"director","manager"} represent? Does the 1 represent kind of an "or" function? Also, is there a way to exclude the text "Sr. director" from this formula? I really don't want to do the lookup if it finds "Sr. director". "T. Valko" wrote: So, you want to lookup G10 and if either Director or Manager is within that string *and* if J10=5000 then do this lookup: VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,FALSE) If Director or Manager is not found *but* J10=5000 then return G10, otherwise return 0. Ok, try this... I'm assuming all of lookup values *do exist* : =IF(AND(COUNT(1/SEARCH({"director","manager"}, VLOOKUP(G10,'[Active employees 3.2.08.xls] Data'!A:N,14,0))),J10=5000), VLOOKUP(N10,'[Active employees 3.2.08.xls] Approvers'!A:O,2,0),IF(J10=5000,G10,0)) -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thanks, I think I now have some sort of order issue here... =IF(AND(ISERROR(OR(SEARCH("Director",VLOOKUP(G10,'[Active employees 3.2.08.xls]data'!$A:$N,14,FALSE),1),ISERROR(SEARCH("Manager", VLOOKUP(G10,'[Active employees 3.2.08.xls]data'!$A:$N,14,FALSE),1)))),J10=5000),VLOOKUP(N10, '[Active employees 3.2.08.xls]Approvers'!$A:$O,2,FALSE),IF(J10=5000,G10,0)) What I want to do is, lookup ("Director" or "Manager") in a job title field. If either of those are TRUE AND J10 = 5000, return approver "C". If both are false, enter approver "A". I keep getting approver "A" even though the Job title has neither "Director" or "Manager". Any adise would be appreciated. Jason "T. Valko" wrote: if either/or: =if(iserror(OR(FIND("TEXT",A1),FIND("TEXT2",A1))), value if text not found, value if text found) That won't work. Replace "TEXT" with TEST: =IF(ISERROR(OR(FIND("TEST",A1),FIND("TEXT2",A1))), "not found","found") A1 = This is a TEST A2 = This is TEXT2 Also, FIND is case sensitive, Test will not match TEST. Better to use SEARCH unless you specifically want to make the condition case sensitive. =IF(COUNT(1/SEARCH({"test","text2"},A1)),"found","not found") Another thing to take into consideration is "false positives" : A1 = This is a contest =IF(COUNT(1/SEARCH({"test","text2"},A1)),"found","not found") Will return found -- Biff Microsoft Excel MVP "Sean Timmons" wrote in message ... Depends on if you want to find where both are in the cell or only need 1. If you need both: =if(iserror(AND(FIND("TEXT",A1),FIND("TEXT2",A1))) ,value if text not found, value if text found) if either/or: =if(iserror(OR(FIND("TEXT",A1),FIND("TEXT2",A1))), value if text not found, value if text found) "T. Valko" wrote: Is the text you're looking for at random spots of the string, like this: this is TEXT some TEXT2 here Or, is the text you're looking for at the beginning of the string, like this: TEXT is here TEXT2 is also here How abount posting some *REAL* examples of the strings *and* the text you're looking for? -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thanks for the info, that worked. Also, what if I want to find multiple words, example "TEXT" and TEXT2". How can I do that? "Sean Timmons" wrote: Or, if the text may be somewhere in the middle of a sentence, =if(iserror(FIND("TEXT",A1)),value if text not found, value if text found) "T. Valko" wrote: *Maybe* this: =IF(LEFT(A1,4)="text",value_if_true,value_if_false ) You can't directly use wildcards with IF. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Normally you set up as A1 = 1, 'value if true', 'value if false'. Can you set one up such that A1= "Text" if A1 contains "Text is a value" I've tried entering A1="Text*" I thought maybe a wildcard, but how can I get my formula to return the true value withough altering my A1 cell that says "Text is a value". Any help would be appreciated. Jason |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If formula and text search
Post several representative examples of the text strings that contain
Director and/or Sr. Director. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thank you very much, it seems to have worked. A couple of followup questions though. What does the count(1/search({"director","manager"} represent? Does the 1 represent kind of an "or" function? Also, is there a way to exclude the text "Sr. director" from this formula? I really don't want to do the lookup if it finds "Sr. director". "T. Valko" wrote: So, you want to lookup G10 and if either Director or Manager is within that string *and* if J10=5000 then do this lookup: VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,FALSE) If Director or Manager is not found *but* J10=5000 then return G10, otherwise return 0. Ok, try this... I'm assuming all of lookup values *do exist* : =IF(AND(COUNT(1/SEARCH({"director","manager"}, VLOOKUP(G10,'[Active employees 3.2.08.xls] Data'!A:N,14,0))),J10=5000), VLOOKUP(N10,'[Active employees 3.2.08.xls] Approvers'!A:O,2,0),IF(J10=5000,G10,0)) -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thanks, I think I now have some sort of order issue here... =IF(AND(ISERROR(OR(SEARCH("Director",VLOOKUP(G10,'[Active employees 3.2.08.xls]data'!$A:$N,14,FALSE),1),ISERROR(SEARCH("Manager", VLOOKUP(G10,'[Active employees 3.2.08.xls]data'!$A:$N,14,FALSE),1)))),J10=5000),VLOOKUP(N10, '[Active employees 3.2.08.xls]Approvers'!$A:$O,2,FALSE),IF(J10=5000,G10,0)) What I want to do is, lookup ("Director" or "Manager") in a job title field. If either of those are TRUE AND J10 = 5000, return approver "C". If both are false, enter approver "A". I keep getting approver "A" even though the Job title has neither "Director" or "Manager". Any adise would be appreciated. Jason "T. Valko" wrote: if either/or: =if(iserror(OR(FIND("TEXT",A1),FIND("TEXT2",A1))), value if text not found, value if text found) That won't work. Replace "TEXT" with TEST: =IF(ISERROR(OR(FIND("TEST",A1),FIND("TEXT2",A1))), "not found","found") A1 = This is a TEST A2 = This is TEXT2 Also, FIND is case sensitive, Test will not match TEST. Better to use SEARCH unless you specifically want to make the condition case sensitive. =IF(COUNT(1/SEARCH({"test","text2"},A1)),"found","not found") Another thing to take into consideration is "false positives" : A1 = This is a contest =IF(COUNT(1/SEARCH({"test","text2"},A1)),"found","not found") Will return found -- Biff Microsoft Excel MVP "Sean Timmons" wrote in message ... Depends on if you want to find where both are in the cell or only need 1. If you need both: =if(iserror(AND(FIND("TEXT",A1),FIND("TEXT2",A1))) ,value if text not found, value if text found) if either/or: =if(iserror(OR(FIND("TEXT",A1),FIND("TEXT2",A1))), value if text not found, value if text found) "T. Valko" wrote: Is the text you're looking for at random spots of the string, like this: this is TEXT some TEXT2 here Or, is the text you're looking for at the beginning of the string, like this: TEXT is here TEXT2 is also here How abount posting some *REAL* examples of the strings *and* the text you're looking for? -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thanks for the info, that worked. Also, what if I want to find multiple words, example "TEXT" and TEXT2". How can I do that? "Sean Timmons" wrote: Or, if the text may be somewhere in the middle of a sentence, =if(iserror(FIND("TEXT",A1)),value if text not found, value if text found) "T. Valko" wrote: *Maybe* this: =IF(LEFT(A1,4)="text",value_if_true,value_if_false ) You can't directly use wildcards with IF. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Normally you set up as A1 = 1, 'value if true', 'value if false'. Can you set one up such that A1= "Text" if A1 contains "Text is a value" I've tried entering A1="Text*" I thought maybe a wildcard, but how can I get my formula to return the true value withough altering my A1 cell that says "Text is a value". Any help would be appreciated. Jason |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If formula and text search
Manager, Sales - - VLOOKUP(N10,'[Active employees
3.2.08.xls]Approvers'!$A:$O,2,0) Sr. Manager, Sales - VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,0) Director, Sales - VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,0) Director, Marketing - VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,0) Sr. Director, Sales - (J10=5000,G10,0) Sr. Director, Marketing - (J10=5000,G10,0) Sr. Director, Finance - (J10=5000,G10,0) Basically I want any Job title with Manager, and Director, to run the lookup, but not the Sr. Director. I hope that makes sense and it what you asked for. "T. Valko" wrote: Post several representative examples of the text strings that contain Director and/or Sr. Director. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thank you very much, it seems to have worked. A couple of followup questions though. What does the count(1/search({"director","manager"} represent? Does the 1 represent kind of an "or" function? Also, is there a way to exclude the text "Sr. director" from this formula? I really don't want to do the lookup if it finds "Sr. director". "T. Valko" wrote: So, you want to lookup G10 and if either Director or Manager is within that string *and* if J10=5000 then do this lookup: VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,FALSE) If Director or Manager is not found *but* J10=5000 then return G10, otherwise return 0. Ok, try this... I'm assuming all of lookup values *do exist* : =IF(AND(COUNT(1/SEARCH({"director","manager"}, VLOOKUP(G10,'[Active employees 3.2.08.xls] Data'!A:N,14,0))),J10=5000), VLOOKUP(N10,'[Active employees 3.2.08.xls] Approvers'!A:O,2,0),IF(J10=5000,G10,0)) -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thanks, I think I now have some sort of order issue here... =IF(AND(ISERROR(OR(SEARCH("Director",VLOOKUP(G10,'[Active employees 3.2.08.xls]data'!$A:$N,14,FALSE),1),ISERROR(SEARCH("Manager", VLOOKUP(G10,'[Active employees 3.2.08.xls]data'!$A:$N,14,FALSE),1)))),J10=5000),VLOOKUP(N10, '[Active employees 3.2.08.xls]Approvers'!$A:$O,2,FALSE),IF(J10=5000,G10,0)) What I want to do is, lookup ("Director" or "Manager") in a job title field. If either of those are TRUE AND J10 = 5000, return approver "C". If both are false, enter approver "A". I keep getting approver "A" even though the Job title has neither "Director" or "Manager". Any adise would be appreciated. Jason "T. Valko" wrote: if either/or: =if(iserror(OR(FIND("TEXT",A1),FIND("TEXT2",A1))), value if text not found, value if text found) That won't work. Replace "TEXT" with TEST: =IF(ISERROR(OR(FIND("TEST",A1),FIND("TEXT2",A1))), "not found","found") A1 = This is a TEST A2 = This is TEXT2 Also, FIND is case sensitive, Test will not match TEST. Better to use SEARCH unless you specifically want to make the condition case sensitive. =IF(COUNT(1/SEARCH({"test","text2"},A1)),"found","not found") Another thing to take into consideration is "false positives" : A1 = This is a contest =IF(COUNT(1/SEARCH({"test","text2"},A1)),"found","not found") Will return found -- Biff Microsoft Excel MVP "Sean Timmons" wrote in message ... Depends on if you want to find where both are in the cell or only need 1. If you need both: =if(iserror(AND(FIND("TEXT",A1),FIND("TEXT2",A1))) ,value if text not found, value if text found) if either/or: =if(iserror(OR(FIND("TEXT",A1),FIND("TEXT2",A1))), value if text not found, value if text found) "T. Valko" wrote: Is the text you're looking for at random spots of the string, like this: this is TEXT some TEXT2 here Or, is the text you're looking for at the beginning of the string, like this: TEXT is here TEXT2 is also here How abount posting some *REAL* examples of the strings *and* the text you're looking for? -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thanks for the info, that worked. Also, what if I want to find multiple words, example "TEXT" and TEXT2". How can I do that? "Sean Timmons" wrote: Or, if the text may be somewhere in the middle of a sentence, =if(iserror(FIND("TEXT",A1)),value if text not found, value if text found) "T. Valko" wrote: *Maybe* this: =IF(LEFT(A1,4)="text",value_if_true,value_if_false ) You can't directly use wildcards with IF. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Normally you set up as A1 = 1, 'value if true', 'value if false'. Can you set one up such that A1= "Text" if A1 contains "Text is a value" I've tried entering A1="Text*" I thought maybe a wildcard, but how can I get my formula to return the true value withough altering my A1 cell that says "Text is a value". Any help would be appreciated. Jason |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If formula and text search
Well, you'd have to add a separate test just for "Sr. Director". That makes
the formula pretty long: =IF(AND(COUNT(SEARCH("sr. director", VLOOKUP(G10,'[Active employees 3.2.08.xls] Data'!A:N,14,0))),J10=5000),G10, IF(AND(COUNT(1/SEARCH({"director","manager"}, VLOOKUP(G10,'[Active employees 3.2.08.xls] Data'!A:N,14,0))),J10=5000), VLOOKUP(N10,'[Active employees 3.2.08.xls] Approvers'!A:O,2,0),IF(J10=5000,G10,0))) -- Biff Microsoft Excel MVP "Jason" wrote in message ... Manager, Sales - - VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,0) Sr. Manager, Sales - VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,0) Director, Sales - VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,0) Director, Marketing - VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,0) Sr. Director, Sales - (J10=5000,G10,0) Sr. Director, Marketing - (J10=5000,G10,0) Sr. Director, Finance - (J10=5000,G10,0) Basically I want any Job title with Manager, and Director, to run the lookup, but not the Sr. Director. I hope that makes sense and it what you asked for. "T. Valko" wrote: Post several representative examples of the text strings that contain Director and/or Sr. Director. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thank you very much, it seems to have worked. A couple of followup questions though. What does the count(1/search({"director","manager"} represent? Does the 1 represent kind of an "or" function? Also, is there a way to exclude the text "Sr. director" from this formula? I really don't want to do the lookup if it finds "Sr. director". "T. Valko" wrote: So, you want to lookup G10 and if either Director or Manager is within that string *and* if J10=5000 then do this lookup: VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,FALSE) If Director or Manager is not found *but* J10=5000 then return G10, otherwise return 0. Ok, try this... I'm assuming all of lookup values *do exist* : =IF(AND(COUNT(1/SEARCH({"director","manager"}, VLOOKUP(G10,'[Active employees 3.2.08.xls] Data'!A:N,14,0))),J10=5000), VLOOKUP(N10,'[Active employees 3.2.08.xls] Approvers'!A:O,2,0),IF(J10=5000,G10,0)) -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thanks, I think I now have some sort of order issue here... =IF(AND(ISERROR(OR(SEARCH("Director",VLOOKUP(G10,'[Active employees 3.2.08.xls]data'!$A:$N,14,FALSE),1),ISERROR(SEARCH("Manager", VLOOKUP(G10,'[Active employees 3.2.08.xls]data'!$A:$N,14,FALSE),1)))),J10=5000),VLOOKUP(N10, '[Active employees 3.2.08.xls]Approvers'!$A:$O,2,FALSE),IF(J10=5000,G10,0)) What I want to do is, lookup ("Director" or "Manager") in a job title field. If either of those are TRUE AND J10 = 5000, return approver "C". If both are false, enter approver "A". I keep getting approver "A" even though the Job title has neither "Director" or "Manager". Any adise would be appreciated. Jason "T. Valko" wrote: if either/or: =if(iserror(OR(FIND("TEXT",A1),FIND("TEXT2",A1))), value if text not found, value if text found) That won't work. Replace "TEXT" with TEST: =IF(ISERROR(OR(FIND("TEST",A1),FIND("TEXT2",A1))), "not found","found") A1 = This is a TEST A2 = This is TEXT2 Also, FIND is case sensitive, Test will not match TEST. Better to use SEARCH unless you specifically want to make the condition case sensitive. =IF(COUNT(1/SEARCH({"test","text2"},A1)),"found","not found") Another thing to take into consideration is "false positives" : A1 = This is a contest =IF(COUNT(1/SEARCH({"test","text2"},A1)),"found","not found") Will return found -- Biff Microsoft Excel MVP "Sean Timmons" wrote in message ... Depends on if you want to find where both are in the cell or only need 1. If you need both: =if(iserror(AND(FIND("TEXT",A1),FIND("TEXT2",A1))) ,value if text not found, value if text found) if either/or: =if(iserror(OR(FIND("TEXT",A1),FIND("TEXT2",A1))), value if text not found, value if text found) "T. Valko" wrote: Is the text you're looking for at random spots of the string, like this: this is TEXT some TEXT2 here Or, is the text you're looking for at the beginning of the string, like this: TEXT is here TEXT2 is also here How abount posting some *REAL* examples of the strings *and* the text you're looking for? -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thanks for the info, that worked. Also, what if I want to find multiple words, example "TEXT" and TEXT2". How can I do that? "Sean Timmons" wrote: Or, if the text may be somewhere in the middle of a sentence, =if(iserror(FIND("TEXT",A1)),value if text not found, value if text found) "T. Valko" wrote: *Maybe* this: =IF(LEFT(A1,4)="text",value_if_true,value_if_false ) You can't directly use wildcards with IF. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Normally you set up as A1 = 1, 'value if true', 'value if false'. Can you set one up such that A1= "Text" if A1 contains "Text is a value" I've tried entering A1="Text*" I thought maybe a wildcard, but how can I get my formula to return the true value withough altering my A1 cell that says "Text is a value". Any help would be appreciated. Jason |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If formula and text search
thank you ver much that worked.
Jason "T. Valko" wrote: Well, you'd have to add a separate test just for "Sr. Director". That makes the formula pretty long: =IF(AND(COUNT(SEARCH("sr. director", VLOOKUP(G10,'[Active employees 3.2.08.xls] Data'!A:N,14,0))),J10=5000),G10, IF(AND(COUNT(1/SEARCH({"director","manager"}, VLOOKUP(G10,'[Active employees 3.2.08.xls] Data'!A:N,14,0))),J10=5000), VLOOKUP(N10,'[Active employees 3.2.08.xls] Approvers'!A:O,2,0),IF(J10=5000,G10,0))) -- Biff Microsoft Excel MVP "Jason" wrote in message ... Manager, Sales - - VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,0) Sr. Manager, Sales - VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,0) Director, Sales - VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,0) Director, Marketing - VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,0) Sr. Director, Sales - (J10=5000,G10,0) Sr. Director, Marketing - (J10=5000,G10,0) Sr. Director, Finance - (J10=5000,G10,0) Basically I want any Job title with Manager, and Director, to run the lookup, but not the Sr. Director. I hope that makes sense and it what you asked for. "T. Valko" wrote: Post several representative examples of the text strings that contain Director and/or Sr. Director. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thank you very much, it seems to have worked. A couple of followup questions though. What does the count(1/search({"director","manager"} represent? Does the 1 represent kind of an "or" function? Also, is there a way to exclude the text "Sr. director" from this formula? I really don't want to do the lookup if it finds "Sr. director". "T. Valko" wrote: So, you want to lookup G10 and if either Director or Manager is within that string *and* if J10=5000 then do this lookup: VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,FALSE) If Director or Manager is not found *but* J10=5000 then return G10, otherwise return 0. Ok, try this... I'm assuming all of lookup values *do exist* : =IF(AND(COUNT(1/SEARCH({"director","manager"}, VLOOKUP(G10,'[Active employees 3.2.08.xls] Data'!A:N,14,0))),J10=5000), VLOOKUP(N10,'[Active employees 3.2.08.xls] Approvers'!A:O,2,0),IF(J10=5000,G10,0)) -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thanks, I think I now have some sort of order issue here... =IF(AND(ISERROR(OR(SEARCH("Director",VLOOKUP(G10,'[Active employees 3.2.08.xls]data'!$A:$N,14,FALSE),1),ISERROR(SEARCH("Manager", VLOOKUP(G10,'[Active employees 3.2.08.xls]data'!$A:$N,14,FALSE),1)))),J10=5000),VLOOKUP(N10, '[Active employees 3.2.08.xls]Approvers'!$A:$O,2,FALSE),IF(J10=5000,G10,0)) What I want to do is, lookup ("Director" or "Manager") in a job title field. If either of those are TRUE AND J10 = 5000, return approver "C". If both are false, enter approver "A". I keep getting approver "A" even though the Job title has neither "Director" or "Manager". Any adise would be appreciated. Jason "T. Valko" wrote: if either/or: =if(iserror(OR(FIND("TEXT",A1),FIND("TEXT2",A1))), value if text not found, value if text found) That won't work. Replace "TEXT" with TEST: =IF(ISERROR(OR(FIND("TEST",A1),FIND("TEXT2",A1))), "not found","found") A1 = This is a TEST A2 = This is TEXT2 Also, FIND is case sensitive, Test will not match TEST. Better to use SEARCH unless you specifically want to make the condition case sensitive. =IF(COUNT(1/SEARCH({"test","text2"},A1)),"found","not found") Another thing to take into consideration is "false positives" : A1 = This is a contest =IF(COUNT(1/SEARCH({"test","text2"},A1)),"found","not found") Will return found -- Biff Microsoft Excel MVP "Sean Timmons" wrote in message ... Depends on if you want to find where both are in the cell or only need 1. If you need both: =if(iserror(AND(FIND("TEXT",A1),FIND("TEXT2",A1))) ,value if text not found, value if text found) if either/or: =if(iserror(OR(FIND("TEXT",A1),FIND("TEXT2",A1))), value if text not found, value if text found) "T. Valko" wrote: Is the text you're looking for at random spots of the string, like this: this is TEXT some TEXT2 here Or, is the text you're looking for at the beginning of the string, like this: TEXT is here TEXT2 is also here How abount posting some *REAL* examples of the strings *and* the text you're looking for? -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thanks for the info, that worked. Also, what if I want to find multiple words, example "TEXT" and TEXT2". How can I do that? "Sean Timmons" wrote: Or, if the text may be somewhere in the middle of a sentence, =if(iserror(FIND("TEXT",A1)),value if text not found, value if text found) "T. Valko" wrote: *Maybe* this: =IF(LEFT(A1,4)="text",value_if_true,value_if_false ) You can't directly use wildcards with IF. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Normally you set up as A1 = 1, 'value if true', 'value if false'. Can you set one up such that A1= "Text" if A1 contains "Text is a value" I've tried entering A1="Text*" I thought maybe a wildcard, but how can I get my formula to return the true value withough altering my A1 cell that says "Text is a value". Any help would be appreciated. Jason |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If formula and text search
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Jason" wrote in message ... thank you ver much that worked. Jason "T. Valko" wrote: Well, you'd have to add a separate test just for "Sr. Director". That makes the formula pretty long: =IF(AND(COUNT(SEARCH("sr. director", VLOOKUP(G10,'[Active employees 3.2.08.xls] Data'!A:N,14,0))),J10=5000),G10, IF(AND(COUNT(1/SEARCH({"director","manager"}, VLOOKUP(G10,'[Active employees 3.2.08.xls] Data'!A:N,14,0))),J10=5000), VLOOKUP(N10,'[Active employees 3.2.08.xls] Approvers'!A:O,2,0),IF(J10=5000,G10,0))) -- Biff Microsoft Excel MVP "Jason" wrote in message ... Manager, Sales - - VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,0) Sr. Manager, Sales - VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,0) Director, Sales - VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,0) Director, Marketing - VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,0) Sr. Director, Sales - (J10=5000,G10,0) Sr. Director, Marketing - (J10=5000,G10,0) Sr. Director, Finance - (J10=5000,G10,0) Basically I want any Job title with Manager, and Director, to run the lookup, but not the Sr. Director. I hope that makes sense and it what you asked for. "T. Valko" wrote: Post several representative examples of the text strings that contain Director and/or Sr. Director. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thank you very much, it seems to have worked. A couple of followup questions though. What does the count(1/search({"director","manager"} represent? Does the 1 represent kind of an "or" function? Also, is there a way to exclude the text "Sr. director" from this formula? I really don't want to do the lookup if it finds "Sr. director". "T. Valko" wrote: So, you want to lookup G10 and if either Director or Manager is within that string *and* if J10=5000 then do this lookup: VLOOKUP(N10,'[Active employees 3.2.08.xls]Approvers'!$A:$O,2,FALSE) If Director or Manager is not found *but* J10=5000 then return G10, otherwise return 0. Ok, try this... I'm assuming all of lookup values *do exist* : =IF(AND(COUNT(1/SEARCH({"director","manager"}, VLOOKUP(G10,'[Active employees 3.2.08.xls] Data'!A:N,14,0))),J10=5000), VLOOKUP(N10,'[Active employees 3.2.08.xls] Approvers'!A:O,2,0),IF(J10=5000,G10,0)) -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thanks, I think I now have some sort of order issue here... =IF(AND(ISERROR(OR(SEARCH("Director",VLOOKUP(G10,'[Active employees 3.2.08.xls]data'!$A:$N,14,FALSE),1),ISERROR(SEARCH("Manager", VLOOKUP(G10,'[Active employees 3.2.08.xls]data'!$A:$N,14,FALSE),1)))),J10=5000),VLOOKUP(N10, '[Active employees 3.2.08.xls]Approvers'!$A:$O,2,FALSE),IF(J10=5000,G10,0)) What I want to do is, lookup ("Director" or "Manager") in a job title field. If either of those are TRUE AND J10 = 5000, return approver "C". If both are false, enter approver "A". I keep getting approver "A" even though the Job title has neither "Director" or "Manager". Any adise would be appreciated. Jason "T. Valko" wrote: if either/or: =if(iserror(OR(FIND("TEXT",A1),FIND("TEXT2",A1))), value if text not found, value if text found) That won't work. Replace "TEXT" with TEST: =IF(ISERROR(OR(FIND("TEST",A1),FIND("TEXT2",A1))), "not found","found") A1 = This is a TEST A2 = This is TEXT2 Also, FIND is case sensitive, Test will not match TEST. Better to use SEARCH unless you specifically want to make the condition case sensitive. =IF(COUNT(1/SEARCH({"test","text2"},A1)),"found","not found") Another thing to take into consideration is "false positives" : A1 = This is a contest =IF(COUNT(1/SEARCH({"test","text2"},A1)),"found","not found") Will return found -- Biff Microsoft Excel MVP "Sean Timmons" wrote in message ... Depends on if you want to find where both are in the cell or only need 1. If you need both: =if(iserror(AND(FIND("TEXT",A1),FIND("TEXT2",A1))) ,value if text not found, value if text found) if either/or: =if(iserror(OR(FIND("TEXT",A1),FIND("TEXT2",A1))), value if text not found, value if text found) "T. Valko" wrote: Is the text you're looking for at random spots of the string, like this: this is TEXT some TEXT2 here Or, is the text you're looking for at the beginning of the string, like this: TEXT is here TEXT2 is also here How abount posting some *REAL* examples of the strings *and* the text you're looking for? -- Biff Microsoft Excel MVP "Jason" wrote in message ... Thanks for the info, that worked. Also, what if I want to find multiple words, example "TEXT" and TEXT2". How can I do that? "Sean Timmons" wrote: Or, if the text may be somewhere in the middle of a sentence, =if(iserror(FIND("TEXT",A1)),value if text not found, value if text found) "T. Valko" wrote: *Maybe* this: =IF(LEFT(A1,4)="text",value_if_true,value_if_false ) You can't directly use wildcards with IF. -- Biff Microsoft Excel MVP "Jason" wrote in message ... Normally you set up as A1 = 1, 'value if true', 'value if false'. Can you set one up such that A1= "Text" if A1 contains "Text is a value" I've tried entering A1="Text*" I thought maybe a wildcard, but how can I get my formula to return the true value withough altering my A1 cell that says "Text is a value". Any help would be appreciated. Jason |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Search for Text within a Formula Result Q | Excel Worksheet Functions | |||
| vlookup formula fails to return data search item is text | Excel Worksheet Functions | |||
| Text search within a string using formula | Excel Worksheet Functions | |||
| Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
| Search column and move text formula | Excel Discussion (Misc queries) | |||
Linear Mode
