Forum Discussion

Shane10326's avatar
Shane10326
Copper Contributor
Dec 30, 2019
Solved

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 v...
excel
Formulas and Functions
  • JKPieterse's avatar
    JKPieterse
    Jan 02, 2020

    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.

JKPieterse's avatar
JKPieterse
Silver Contributor
Dec 30, 2019
Is there a rule that determines how many characters we need to take from the postal code?
    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      Dec 31, 2019
      Which 3 rules?
      • Shane10326's avatar
        Shane10326
        Copper Contributor
        Dec 31, 2019
        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.

Resources