IF Function with Multiple Criteria

**excel_learner** · 08-08-2011, 11:43 AM

**Admin** · 08-08-2011, 02:13 PM

HI,

Try

=IF(ISNA(MATCH(I3,{14,51,61},0)),IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*20653750)/$D$3257,0),IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U 3*150030)/$D$3262,0),0))

You could replace the array (in red ) with a range as well.

**excel_learner** · 06-03-2012, 01:40 PM

Originally Posted by Admin

HI,

Try

=IF(ISNA(MATCH(I3,{14,51,61},0)),IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*20653750)/$D$3257,0),IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U 3*150030)/$D$3262,0),0))

You could replace the array (in red ) with a range as well.

In the above formula if I want to add another range match(N3,{CA,CN,KR} without changing the first one , how would I do that?

Kindly assist.

**excel_learner** · 08-08-2011, 03:41 PM

Great, its working. if i want other way arround i have to remove ISNA, is it?
Thanks

**Admin** · 08-08-2011, 03:57 PM

Hi,

No, ISNA will still be there. Just replace those array of values with a range.

**Admin** · 06-03-2012, 08:14 PM

Hi

Not sure whether you want to match or not to match.

1. to match N3 with the array

=IF(ISNA(MATCH(I3,{14,51,61},0)),IF(MATCH(N3,{"CA" ,"CN","KR"},0),IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*206 53750)/$D$3257,0),IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U 3*150030)/$D$3262,0),0)))

2. This will exclude the match

=IF(ISNA(MATCH(I3,{14,51,61},0)),IF(ISNA(MATCH(N3, {"CA","CN","KR"},0)),IF(AND(K3>=$KP$1,K3<=$KQ$1),( U3*20653750)/$D$3257,0),IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U 3*150030)/$D$3262,0),0)))

**excel_learner** · 06-04-2012, 10:34 AM

Thanks, however, the second formula gives me FALSE for values excluded in first match i.e 14,51,61. And first formula gives me N/A#. Can this be fixed with and / or, as i want both match excluded from the calculation. e.g if it finds 14,51,61 and also ca,cn,kr the formula should return 0.

I hope the above is clear.

**Admin** · 06-04-2012, 01:46 PM

Hi

Does this

=IF(ISNA(MATCH(I3,{14,51,61},0)),IF(ISNA(MATCH(N3, {"CA","CN","KR"},0)),IF(AND(K3>=$KP$1,K3<=$KQ$1),( U3*20653750)/$D$3257,IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*1 50030)/$D$3262,0),0))),0)

correct result ?

**excel_learner** · 06-04-2012, 02:15 PM

not working bracket error, my original formula is as below, it does not move,

=IF(ISNA(MATCH(I3,PC_CC,0)),IF(ISNA(MATCH(R3,Risk_ country,0)),IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*206537 50)/$D$4772,0),IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U 3*150030)/$D$4779,0),0))),0) where pc_cc includes 14,51,33 etc and risk_country includes CN, RU, etc.

and if i change to

=IF(ISNA(MATCH(I3,PC_CC,0)),IF(ISNA(MATCH(R3,Risk_ country,0)),IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*206537 50)/$D$4772,0),IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U 3*150030)/$D$4779,0),0)),0)

it does not perform the IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*150030)/$D$4779,0),0)),0) returns 0.

**Admin** · 06-04-2012, 02:40 PM

=IF(ISNA(MATCH(I3,PC_CC,0)),IF(ISNA(MATCH(R3,Risk_ country,0)),IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*206537 50)/$D$4772,IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*1 50030)/$D$4779),0)),0),0)