Forum Discussion

Anndie's avatar
Anndie
Copper Contributor
Mar 08, 2022
Solved

IFS formula returns #NAME? error

Hi everyone. I'm new to this forum I am Canadian, and I am working on an invoice log. I created a dropdown list in my spreadsheet (column D) with all Canadian provinces, and now I would like column ...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Mar 08, 2022

    Anndie 

    You have used so-called curly quotes ” around AB etc.

    Excel expects straight quotes "

    =IFS(D2="AB",0.0005*100,D2="BC",0.0005*100,D2="MB",0.0005*100,D2="NB",0.0015*100,D2="NL",0.0015*100,D2="NT",0.0005*100,D2="NS",0.0015*100,D2="NU",0.0005*100,D2="ON",0.0013*100,D2="PEI",0.0015*100,D2="QC",0.0005*100,D2="SK",0.0005*100,D2="YT",0.0005*100)

    A shorter version:

    =IFS(OR(D2={"AB","MB","BC","NT","NU","QC","SK","YT"}),0.0005,OR(D2={"NB","NL","NS","PEI"}),0.0015,D2="ON",0.0013)*100

turuu223's avatar
turuu223
Copper Contributor
Jan 17, 2023

HansVogelaar no i don't now R1C1 reference

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 17, 2023

    turuu223 

    Strange - your formula is syntactically correct. Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

Resources