Forum Discussion
IFS function #N/A
Hi all,
I have built a formula (below) to help organise postcodes into patches, my formula shows a true value when I walk through it but returns #N/A.
I have tested this across a number of variables and it seems there is an overlap, example: AB1 2CD would be true and AB12 3CD would also be true, i have used left a1,3 left a1,4 to try and separate the two "similar" postcodes because using wildcards didnt get me anywhere, I just can get it to work.
Any ideas???
=IFS
(LEFT(E2,3)={"SL1","SL2","SL3","SL4","SL6","SL7","SL8"},1,
LEFT(E2,4)={"HP10","HP11","HP12"},1,
LEFT(E2,3)={"RG1","RG2","RG4","RG5","RG6","RG7","RG8","RG9"},2,
LEFT(E2,4)={"RG10","RG30","RG31"},2,
LEFT(E2,3)="SL5",3,
LEFT(E2,4){"GU14","GU15","GU16","GU17","GU18","GU19","GU20","GU25","GU46","GU47","GU51","GU52","RG12","RG40","RG41","RG42","RG45"},3,
LEFT(E2,4){"RG14","RG17","RG18","RG19","RG20","RG21","RG22","RG23","RG24","RG25","RG26","RG27","RG28","RG29"},4,
LEFT(E2,2)={"DT","BH"},5,
LEFT(E2,3)={"SP1","SP2","SP3","SP4","SP5","SP6","SP7","SP8"},5,
LEFT(E2,3)="SP9",6,
LEFT(E2,4)={"SP10","SP11"},6,
LEFT(E2,2)="SO",6,
LEFT(E2,2)="PO",7,
LEFT(E2,4)={"GU26","GU29","GU30","GU31","GU32","GU33","GU34","GU35"},7)
Shane10326 I created a table with the patch groupings from your formula. I expect if you make sure the table remains sorted on the first column, the formulas in E3 and F3 return the correct code and patch number from the postal code.
11 Replies
Shane10326 I created a table with the patch groupings from your formula. I expect if you make sure the table remains sorted on the first column, the formulas in E3 and F3 return the correct code and patch number from the postal code.
- This is perfect, table will be in a separate sheet and formula works across all bases.
Thank you
- Your formula defies logic! For example, if the value of E2 is RG12, it either returns 2 if 3 characters are extracted or 3 if 4 characters are extracted but it doesn’t specify when 3 or 4 characters should be extracted. Thus, I suggest you set up those characters in a tabular format and use VLOOKUP, exact match, to return your desired results.
Thank you but how would I specify exact match for the first 3 or 4 characters? I couldnt do the whole postcode as i would have near a thousand.
Is there no wildcard or even blank space I can use with the IFS formula?
- Is there a rule that determines how many characters we need to take from the postal code?
Yes 3 different rules JKPieterse
- Which 3 rules?
