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?

#	A	B	C	D	E	F	G
1			11858594	5			FALSE
2			11858275	6			010066743
3			11857038	3			99006661001
4			11858479	9			52006881570
5			61674304	6			FALSE
6			11854352	5			FALSE
7			11858622	6			010066743
8			11858520	5			FALSE
9			11858132	5			FALSE
10			11858124	6			010066743
11			62367347	6			FALSE
12			11858488	5			FALSE
13			11858698	6			010066743
14			63158920	6			FALSE
15			11858655	6			010066743
16			11858534	5			FALSE

<tbody>
</tbody>

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

	C	D	E	F	G
2	11858594	5			010066743
3	11858275	6			010066743
4	11857038	3			99006661001
5	11858479	9			52006881570
6	61674304	6			FALSE
7	11854352	5			010066743
8	11858622	6			010066743
9	11858520	5			010066743
10	11858132	5			010066743
11	11858124	6			010066743
12	62367347	6			FALSE
13	11858488	5			010066743
14	11858698	6			010066743
15	63158920	6			FALSE
16	11858655	6			010066743
17	11858534	5			010066743

<tbody>
</tbody>

Spreadsheet Formulas

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>

<tbody>
</tbody>

 

[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

<tbody>
</tbody>

=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.