IF statement

[drjpb]

I am trying to write an IF condition, first criteria is Column D,
If Column D Value is 3, then Result value should be 99006661001
If Column D Value is 9, then Result Value should be 52006881570
If Column D Value is 6 or 5, then second criteria is based on Column C (First 2 digits),
If first two digits of C is 11, then Result Value should be 010066743
If first two digits of C is 12, then Result Value should be 020066743
If first two digits of C is 13, then Result Value should be 030066743
If first two digits of C is 14, then Result Value should be 520066743
If first two digits of C is 15, then Result Value should be 050066743
If first two digits of C is 16, then Result Value should be 050066743

Now the problem i am facing is, with Column D value 5, why is it giving me False?


[TABLE="width: 500"]
<tbody>[TR]
[TD]#[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]11858594[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]11858275[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]010066743[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]11857038[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]99006661001[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]11858479[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]52006881570[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]61674304[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]11854352[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]11858622[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]010066743[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]11858520[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]11858132[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]11858124[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]010066743[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]62367347[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]11858488[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD]11858698[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]010066743[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD]63158920[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]11858655[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]010066743[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD]11858534[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]



Here's my formula, typed in Range G2:

=IF(D2=3;"99006661001";(IF(D2=9;"52006881570";
(IF(D2=6;(IF(LEFT(C2;2)="11";"010066743";IF(LEFT(C2;2)="12";"020066743";
IF(LEFT(C2;2)="13";"030066743";IF(LEFT(C2;2)="14";"520066743";
IF(LEFT(C2;2)="15";"050066743";IF(LEFT(C2;2)="16";"050066743";
(IF(D2=5;(IF(LEFT(C2;2)="11";"010066743";IF(LEFT(C2;2)="12";
"020066743";IF(LEFT(C2;2)="13";"030066743";IF(LEFT(C2;2)="14";
"520066743";IF(LEFT(C2;2)="15";"050066743";IF(LEFT(C2;2)="16";
"050066743")))))))))))))))))))))

Will appreciate if my mistake is rectified.
Thanks in advance.

 

[etaf]

you are onlytesting D2 for values of 3,9,6
(IF(D2=6;

You want to add
(IF(OR( D2=6; D2=5) ;
so that if D2 is 5 or a 6 you do the C column Test

its also false because you dont have a false value at the end

520066743";IF(LEFT(C2;2)="15";"050066743";IF(LEFT(C2;2)="16";
"050066743";"you need a value here or you get false")))))))))))))))))))))

 

[ukmikeb]

Hi

Or :-

=IF(D2=3;"99006661001";IF(D2=9;"52006881570";IF(OR(D2={5;6});IFERROR(INDEX({"010066743";"020066743";"030066743";"520066743";"050066743";"050066743"};;MATCH(LEFT(C2;2);{"11";"12";"13";"14";"15";"16"};0));FALSE))))

hth

 

[exceldevs]

Hi drjpb,

Base on your requirements in post#1, this might be what you're looking for.
ps: Note that if none of the condition met you're end up with FALSE (as Wayne highlighted)
ps: Please replace all commas with semi-colons to match your settings

[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: #CACACA"]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: right"]11858594[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]010066743[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: right"]11858275[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]010066743[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: right"]11857038[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99006661001[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: right"]11858479[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]52006881570[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: right"]61674304[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="align: right"]11854352[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]010066743[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: right"]11858622[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]010066743[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="align: right"]11858520[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]010066743[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="align: right"]11858132[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]010066743[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]11[/TD]
[TD="align: right"]11858124[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]010066743[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]12[/TD]
[TD="align: right"]62367347[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]13[/TD]
[TD="align: right"]11858488[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]010066743[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]14[/TD]
[TD="align: right"]11858698[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]010066743[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]15[/TD]
[TD="align: right"]63158920[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]16[/TD]
[TD="align: right"]11858655[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]010066743[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]17[/TD]
[TD="align: right"]11858534[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]010066743[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Spreadsheet Formulas[/TD]
[/TR]
[TR]
[TD]

Cell	Formula
G2	=IF(D2=3,"99006661001",IF(D2=9,"52006881570",IF(OR(D2={5,6}),IF(LEFT(C2,2)="11","010066743",IF(LEFT(C2,2)="12","020066743",IF(LEFT(C2,2)="13","030066743",IF(LEFT(C2,2)="14","520066743",IF(OR(LEFT(C2,2)={"15","16"}),"050066743"))))))))

<tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Drrellik]

Good Morning DR, looks like Wayne beat me to it this morning, I am at work adn cant get the the table to copy and paste like I can at home. but Wayne is right if you consider changing your logic a bit how about a lookup table. with an IF(OR()) in it. I took your values and made a table from I1:J8 starting with [TABLE="width: 83"]
<tbody>[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
=IF(OR(D14=5,D14=6),VLOOKUP(LEFT(C14,2)*1,$I$1:$J$8,2,FALSE),IF(D14=3,$J$1,IF(D14=9,$J$2,"")))

3,9,11,12,13,14,15,16 in I and the rest of your desired results in K. you can alsways expand your range or change your results in the future without changing your formula.

 

[drjpb]

Thanks guys for all the solutions, and your efforts. I will try all tomorrow morning in office, and will post the results.
Once again thanking you for your care.