liamlarmour
New Member
- Joined
- May 4, 2017
- Messages
- 4
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.
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
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
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.
Column | A | B | C | D |
Row | Number of Digits | First Two | First Three | Number Type |
1 | 11 | 02 | 028 | NI Landline |
2 | 11 | 07 | 077 | Mobile |
3 | 13 | 26 | 269 | Other Number |
4 | 11 | 02 | 028 | NI Landline |
5 | 11 | 02 | 028 | NI Landline |
6 | 11 | 07 | 077 | Mobile |
7 | 11 | 02 | 028 | NI Landline |
8 | 11 | 07 | 079 | Mobile |
9 | 11 | 07 | 077 | Mobile |
10 | 11 | 02 | 028 | NI Landline |
11 | 11 | 07 | 077 | Mobile |
12 | 11 | 02 | 028 | NI Landline |
13 | 11 | 02 | 028 | NI Landline |
14 | 11 | 02 | 028 | NI Landline |
15 | 11 | 02 | 028 | NI Landline |
16 | 11 | 07 | 078 | Mobile |
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
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