IFS function #N/A

[Shane10326]

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)

[#2]

Is there a rule that determines how many characters we need to take from the postal code?

[#3]

Yes 3 different rules @Jan Karel Pieterse 

[#4]

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.

[#5]

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?

 @Twifoo 

[#6]

Which 3 rules?

[#7]

LEFT(E2,2)
LEFT(E2,3)
LEFT(E2,4)

The ,3 and the ,4 clash and i have tried removing ,3 all together and then using * or ? or "" but the formula still doesnt work as i need it to.

[#8]

I guess I didn't make myself clear. Can you come up with a general rule as to when we need to use 2, 3 or 4 characters from the code?

[#9]

begins with SO, PO, DT, BH first 2 characters
these would return 5 6 or 7

beggins with RG*, SL*, SP* first 3 characters ( letters followed by numbers 0 through to 9 then a blank space )
these would return 1 2 3 5 or 6

begins with RG**, HP**, GU**, first 4 characters ( letters followed by numbers 11 through to 50 )
these would return 1 2 3 4 or 7


The only general rule i can come up with is always looking at the first 4 characters, however the last character will be a blank space a lot of the time.

[#10]

Your use of “or” is disallowed! You must specify under which circumstances will the formula return a “definitive” result.

[#11 · Accepted solution]

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

[#12]

This is perfect, table will be in a separate sheet and formula works across all bases.

Thank you