More than 1 TRUE value in and IF formula

[Teddymanne]

Hi,

I want to try this:
+IF(MID(B9;3;4)="P01E","P02E","P09E");$B$87)

So if it's P01E or P02E or P09E: in all 3 cases it should refer to the B87 cel.

Hopefully my question is clear.

Thanks in advance,

[Jonmo1]

Try

=IF(OR(MID(B9;3;4)={"P01E";"P02E";"P09E"});$B$87)

[Teddymanne]

Thanks man, it seems like it worked out with the brackets!

[Jonmo1]

You're welcome.

[Teddymanne]

And how could I do this?: I want my price to be +0,89€ when it's P02E, P09E or P01E BUT when it's for example P46E, P37E, P08E... (every other number that's not P01E, P09E or P01E) I want it + 1,19€

Thanks in advance

[Jonmo1]

Try
=IF(OR(MID(B9;3;4)={"P01E";"P02E";"P09E"});$B$87+0,89;$B$87+1,19)

[Teddymanne]

No, that won't work because I have other options, for example N09E + 2,50€.

So if the first didget is 'P' and the last is 'E' and the middle ones are '01', '02' or '09' it should be +0,89€, in any other case (2 middle digits are not one of those 3 but first and last stays 'P' and 'E' it should be +1,19€.

[Jonmo1]

I would build a table to translate the code to the value..

Say E1:F6

E1:E6 = P01E P02E P09E N01E N02E N09E
F1:F6 = 0,89 0,89 0,89 1,19 1,19 1,19

Then use

=VLOOKUP(MID(B9;3;4);$E$1:$F$6;2;FALSE)*$B$87

[Teddymanne]

If tried this:
+IF(MID(B9;3;1)="P";IF(MID(B9;6;1)="E";IF(MID(B9;4;2)={"01";"02";"09"};B87;B88)))

But it's not working, when the two middle digits are for example "02"; it refers to B88 instead of B87, what did I do wrong?

[Jonmo1]

The original formula should be just fine, just add the B88 for the FALSE part..

=IF(OR(MID(B9;3;4)={"P01E";"P02E";"P09E"});$B$87;$B$88)



And you forgot the OR part in your attempt above...

[Teddymanne]

No, that won't work because if I have now for example a N09E or an A00B it will also give me the B88 value, there are a lot of options.

[Teddymanne]

The strange thing is my formula works only with the "01" and for the "02" and "09" he gives me the B88 value.

EDIT: now it worked, I forgot to add the OR command in my function. Many thanks for your help, I hope I can advance right now.

[Jonmo1]

I'll repeat post #8 then.

build a table with all the possible values of MID(B9;3;4) in one column, and the corresponding values in the column to the right.
And use Vlookup.

[Teddymanne]

So finally after a lot of testing it worked with this code:
=IF($E$6="N";IF($B$8=1180;$G$97;$G$83);IF($B$8=1180;IF(IF($B9=$B12;$E11+$E14;$E11)>500;$G$97;IF(IF($B9=$B12;$E11+$E14;$E11)>200;$F$97;$E$97));IF(IF($B9=$B12;$E11+$E14;$E11)>500;$G$83;IF(IF($B9=$B12;$E11+$E14;$E11)>200;$F$83;$E$83))))+IF(MID(B9;1;1)="R";$H$97)+IF(MID(B9;1;1)="Z";$H$83)+IF(MID(B9;3;4)="A00B";$B$75)+IF(MID(B9;8;4)="A00B";$B$75)+IF(MID(B9;3;1)="P";IF(MID(B9;6;1)="E";IF(OR(MID(B9;4;2)={"09";"02";"01"});$B$77;$B$78)))+IF(MID(B9;8;1)="P";IF(MID(B9;11;1)="E";IF(OR(MID(B9;9;2)={"09";"02";"01"});$B$77;$B$78)))+IF(MID(B9;3;1)="P";IF(MID(B9;6;1)="B";IF(OR(MID(B9;4;2)={"09";"02";"01"});0;$B$76)))+IF(MID(B9;8;1)="P";IF(MID(B9;11;1)="B";IF(OR(MID(B9;9;2)={"09";"02";"01"});0;$B$76)))+IF(MID(B9;3;1)="P";IF(MID(B9;6;1)="H";IF(MID(B9;4;2)="02";$B$79;$B$80)))+IF(MID(B9;8;1)="P";IF(MID(B9;11;1)="H";IF(MID(B9;9;2)="02";$B$79;$B$80)))+IF(MID(B9;3;1)="Q";IF(MID(B9;6;1)="B";IF(OR(MID(B9;4;2)={"09";"02"});$B$81)))+IF(MID(B9;8;1)="Q";IF(MID(B9;11;1)="B";IF(OR(MID(B9;9;2)={"09";"02"});$B$81)))+IF(MID(B9;3;1)="Q";IF(MID(B9;6;1)="E";IF(OR(MID(B9;4;2)={"09";"02"});$B$82)))+IF(MID(B9;8;1)="Q";IF(MID(B9;11;1)="B";IF(OR(MID(B9;9;2)={"09";"02"});$B$82)))+IF(MID(B9;3;1)="K";IF(MID(B9;6;1)="B";IF(OR(MID(B9;4;2)={"09";"02"});$B$83)))+IF(MID(B9;8;1)="K";IF(MID(B9;11;1)="B";IF(OR(MID(B9;9;2)={"09";"02"});$B$83)))+IF(MID(B9;3;1)="K";IF(MID(B9;6;1)="H";IF(OR(MID(B9;4;2)={"09";"02"});$B$84)))+IF(MID(B9;8;1)="H";IF(MID(B9;11;1)="B";IF(OR(MID(B9;9;2)={"09";"02"});$B$84)))+IF(MID(B9;3;1)="N";IF(MID(B9;6;1)="B";IF(MID(B9;4;2)="09";$B$85;$B$86)))+IF(MID(B9;8;1)="N";IF(MID(B9;11;1)="B";IF(MID(B9;9;2)="09";$B$85;$B$86)))+IF(MID(B9;3;1)="N";IF(MID(B9;6;1)="E";IF(MID(B9;4;2)="09";$B$87;$B$88)))+IF(MID(B9;8;1)="N";IF(MID(B9;11;1)="E";IF(MID(B9;9;2)="09";$B$87;$B$88)))+IF(MID(B9;3;1)="M";IF(MID(B9;6;1)="E";IF(MID(B9;4;2)="09";$B$89;$B$90)))+IF(MID(B9;8;1)="M";IF(MID(B9;11;1)="E";IF(MID(B9;9;2)="09";$B$89;$B$90)))+IF(MID(B9;3;1)="D";IF(MID(B9;6;1)="E";$B$91))+IF(MID(B9;8;1)="D";IF(MID(B9;11;1)="E";$B$91))+IF(MID(B9;3;1)="4";IF(MID(B9;6;1)="B";IF(MID(B9;4;2)="09";$B$92)))+IF(MID(B9;8;1)="4";IF(MID(B9;11;1)="B";IF(MID(B9;9;2)="09";$B$92)))+IF(MID(B9;3;1)="4";IF(MID(B9;6;1)="E";IF(MID(B9;4;2)="09";$B$93)))+IF(MID(B9;8;1)="4";IF(MID(B9;11;1)="E";IF(MID(B9;9;2)="09";$B$93)))+IF(MID(B9;3;4)="B005";$B$94)+IF(MID(B9;3;4)="B006";$B$95)+IF(MID(B9;3;4)="L005";$B$96)+IF(MID(B9;3;4)="L006";$B$97)+IF(MID(B9;3;4)="I005";$B$98)+IF(MID(B9;3;4)="I006";$B$99)+IF(MID(B9;3;4)="I003";$B$100)+IF(MID(B9;8;4)="B005";$B$94)+IF(MID(B9;8;4)="B006";$B$95)+IF(MID(B9;8;4)="L005";$B$96)+IF(MID(B9;8;4)="L006";$B$97)+IF(MID(B9;8;4)="I005";$B$98)+IF(MID(B9;8;4)="I006";$B$99)+IF(MID(B9;8;4)="I003";$B$100)


As you can see I've used a lot of your {...} codes which I'm very thankful for!

Thanks or helping me out!