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.
:
Post several representative examples of the text strings that contain
Director and/or Sr. Director.
--
Biff
Microsoft Excel MVP
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".
:
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
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
:
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
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)
:
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
Thanks for the info, that worked. Also, what if I want to
find
multiple
words, example "TEXT" and TEXT2". How can I do that?
:
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)
:
*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
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
