IF Function with Multiple Criteria

[excel_learner]

I have following formula wherein first part of the formula excludes 14 and 51 from the calculation, now i want to add in exclusion 61 as well. Any solution for the flexible formula where i can keep on adding any number to exclusion. kindly assist


=IF(AND(I3<>14,I3<>51),IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*20653750)/$D$3257,0),IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*150030)/$D$3262,0),0))


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vOQApTgb (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vOQApTgb)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vbihZ-7W (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vbihZ-7W)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vfmpSO0F (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vfmpSO0F)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vjfTJ7lX (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vjfTJ7lX)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vmq-LHHz (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vmq-LHHz)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vst3j_7i (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9c-vst3j_7i)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bwBqjIR5 Nj (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bwBqjIR5 Nj)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bwBw8El0 r5 (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bwBw8El0 r5)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bwC63GbR uM (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bwC63GbR uM)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bwC9fyKZ do (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bwC9fyKZ do)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bwCEn8DB Qe (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bwCEn8DB Qe)
https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bw0Bey8g QO (https://www.youtube.com/watch?v=nVy4GAtkh7Q&lc=UgxJGNhWFZh2p5mK0XB4AaABAg.9bbxud383FI9bw0Bey8g QO)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[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),(U3*150030)/$D$3262,0),0))

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

[excel_learner]

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

[Admin]

Hi,

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

[excel_learner]

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),(U3*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.

[Admin]

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*20653750)/$D$3257,0),IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*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),(U3*150030)/$D$3262,0),0)))

[excel_learner]

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]

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*150030)/$D$3262,0),0))),0)

correct result ?

[excel_learner]

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*20653750)/$D$4772,0),IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*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*20653750)/$D$4772,0),IF(I3=14,IF(AND(K3>=$KP$1,K3<=$KQ$1),(U3*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]

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

[excel_learner]

No still it does not work for last formula where it is equal to 14 and it is excluded in first match, returns 0.

[Admin]

Hi

Can you attach a sample workbook with some dummy values along with expected results ?

[excel_learner]

sample is attached and hope i have made it clear.

[Admin]

Hi

I think this is the formula you want.

=IF(AND(ISNA(MATCH(A3,pc_cc,0)),ISNA(MATCH(C3,coun ,0))),IF(AND(A3<>14,B3>=$E$1,B3<=$F$1),(D3*20653750)/172884227,IF(AND(A3=14,B3>=$E$1,B3<=$F$1),(D3*150030)/6083342,0)),0)

and if you evaluate this formula

first it checks

AND(ISNA(MATCH(A3,pc_cc,0)),ISNA(MATCH(C3,coun,0)) )

if it evaluates TRUE, the next step

IF(AND(A3<>14,B3>=$E$1,B3<=$F$1)

.. and if it evaluates TRUE, then it calculates (D3*20653750)/172884227

or if it evaluates FALSE, then evaluates this one.. IF(AND(A3=14,B3>=$E$1,B3<=$F$1)

If it TRUE, (D3*150030)/6083342, otherwise 0

Is it what you are after ?

if not, give me the cell values for E3:E18.

[excel_learner]

I have attached sample data with required values;

Kindly see.

[Admin]

Hi,

In E7, this ISNA(MATCH(A7,pc_cc,0)) returns FALSE.

[excel_learner]

Ok, any way out to get correct values. But this works fine if I dont put the second match which excludes coun.