Multiple IF AND in same formula

[liamlarmour]

Hi

I'm trying to create a formula to deliver three different outputs based on conditions of three columns.

Conditions:

IF a2 = 11 AND d2 = 028 return NI Landline OR
IF a2 = 11 AND c2 = 07 return Mobile OR
IF a2 <>11 return Other Number

It;s necessary to check Col C for 028 to verify it's a NI Landline, and Col B for 07 to verify it's a UK mobile number - both will have 11 digits. Those that don't have 11 digits are likely to be incorrectly formatted numbers or non NI/-UK numbers.

[TABLE="width: 406"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Column[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Row[/TD]
[TD]Number of Digits[/TD]
[TD]First Two [/TD]
[TD]First Three[/TD]
[TD]Number Type[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]11[/TD]
[TD]02[/TD]
[TD]028[/TD]
[TD]NI Landline[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11[/TD]
[TD]07[/TD]
[TD]077[/TD]
[TD]Mobile[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]13[/TD]
[TD]26[/TD]
[TD]269[/TD]
[TD]Other Number[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]11[/TD]
[TD]02[/TD]
[TD]028[/TD]
[TD]NI Landline[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]11[/TD]
[TD]02[/TD]
[TD]028[/TD]
[TD]NI Landline[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]11[/TD]
[TD]07[/TD]
[TD]077[/TD]
[TD]Mobile[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]11[/TD]
[TD]02[/TD]
[TD]028[/TD]
[TD]NI Landline[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]11[/TD]
[TD]07[/TD]
[TD]079[/TD]
[TD]Mobile[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]11[/TD]
[TD]07[/TD]
[TD]077[/TD]
[TD]Mobile[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]11[/TD]
[TD]02[/TD]
[TD]028[/TD]
[TD]NI Landline[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]11[/TD]
[TD]07[/TD]
[TD]077[/TD]
[TD]Mobile[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]11[/TD]
[TD]02[/TD]
[TD]028[/TD]
[TD]NI Landline[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]11[/TD]
[TD]02[/TD]
[TD]028[/TD]
[TD]NI Landline[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]11[/TD]
[TD]02[/TD]
[TD]028[/TD]
[TD]NI Landline[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]11[/TD]
[TD]02[/TD]
[TD]028[/TD]
[TD]NI Landline[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]11[/TD]
[TD]07[/TD]
[TD]078[/TD]
[TD]Mobile[/TD]
[/TR]
</tbody>[/TABLE]


I tried the below formula but it only returns Other Number for all rows:

IF((AND(G2="11",I2="028")),"NI Landline",IF((AND(G2="11",H2="07")),"Mobile","Other Number"))

I guess I am doing something obviously wrong that I'll hit myself once it's pointed out to me, but I would be grateful for any advice.

Thanks
Liam

 

[philwojo]

Have you tried your formula without the quotes around the numbers in your AND statements, like 11 instead of "11", and 028 instead of "028"?

 

[dreid1011]

The only thing I could point out is the extra, unnecessary, parenthesis.

IF((AND(G2="11",I2="028")),"NI Landline",IF((AND(G2="11",H2="07")),"Mobile","Other Number"))

Otherwise, I would make sure your G2 and I2 values are indeed text because the formula is looking for a text value. If they are NOT text, but numbers formatted to keep the leading 0, then you need to remove the "" from the 11, 028 and 07 in the formula.

 

[jtakw]

Have you tried your formula without the quotes around the numbers in your AND statements, like 11 instead of "11", and 028 instead of "028"?

Hi,

Agree with you philwojo, the 11, yes, but probably not the "028" as that Looks like it may be text.

 

[liamlarmour]

Hi folks

Thanks for your replies, apologies for my very late response, got caught up with other workstuff.

The telephone numbers are all text and the 11 is a =LEN() result. I'm still working on this, but have started using some workarounds and shortcuts. I'll keep coming back to this formula as I like the challenge, so I appreciate all of your help.

Liam

 

[jtakw]

Hi folks

Thanks for your replies, apologies for my very late response, got caught up with other workstuff.

The telephone numbers are all text and the 11 is a =LEN() result. I'm still working on this, but have started using some workarounds and shortcuts. I'll keep coming back to this formula as I like the challenge, so I appreciate all of your help.

Liam

Hi,

As suggested above, since your G Column is result of LEN formula, it IS a number and Not text, while the H and I Columns are Text, just adjust your formula like below.
You were getting "Other Number" for all rows because the IF formula testing G2="11" always fails, since G2 is the number 11, Not the Text "11".

Book1
	G	H	I	J
1	Number of Digits	First Two	First Three	Number Type
2	11	02	028	NI Landline
3	11	07	077	Mobile
4	13	26	269	Other Number
5	11	02	028	NI Landline
6	11	02	028	NI Landline
7	9	07	077	Other Number
8	11	02	028	NI Landline
9	11	07	079	Mobile
10	11	07	077	Mobile
11	11	02	028	NI Landline
12	11	07	077	Mobile
13	11	02	028	NI Landline
14	11	02	028	NI Landline
15	11	02	028	NI Landline
16	11	02	028	NI Landline
17	11	07	078	Mobile
Sheet32

Cell Formulas
Range	Formula
J2	=IF(AND(G2=11,I2="028"),"NI Landline",IF(AND(G2=11,H2="07"),"Mobile","Other Number"))