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