calculation based on meeting two criteria
 

I have the following formula and it returns the wrong calculation

K1 =.03
K2 =.02

In this situation, its supposed to multiply F82*K2, instead it is
multiplying F82* K1
=IF(E82="service a",F82*$K$1,IF(AND(E82="Service B",MATCH(C82,'Sheet
2!$C$2:$C$15)),F82*$K$1,F82*$K$2))

any help is appreciated.

To simplify your formula try this:

=IF(OR(E82="service a",AND(E82="Service B",MATCH(C82,'Sheet
2!$C$2:$C$15))),F82*$K$1,F82*$K$2)


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=473965

MATCH(C82,Sheet2!$C$2:$C$15)

You have the match function set up to find an approximate match, not an
exact match. Is this what you intended? If you intend to look for an exact
match, this part of your formula would be problematic. Check help for the
match function and its argument (the third argument, which you've omitted,
specifies the match type. 0 specifies an exact match).

If you do intend to look for an exact match, you will probably need to nest
MATCH inside NOT(ISERROR(MATCH(....))) or NOT(ISNA(MATCH(.....))) to get the
results you want.


"jerry" wrote:

I have the following formula and it returns the wrong calculation

K1 =.03
K2 =.02

In this situation, its supposed to multiply F82*K2, instead it is
multiplying F82* K1
=IF(E82="service a",F82*$K$1,IF(AND(E82="Service B",MATCH(C82,'Sheet
2!$C$2:$C$15)),F82*$K$1,F82*$K$2))

any help is appreciated.