Forum Discussion

Adrien89's avatar
Adrien89
Copper Contributor
Dec 06, 2023

Need help with formula

So I need a hand with sorting this out.  Cell B58 and D58 are drop down menus and I have part pf the list at the bottom. I want to be about to select say "Belyando" and "Brisbane" in the lists and t...
Formulas and Functions
office 365
djclements's avatar
djclements
Bronze Contributor
Dec 07, 2023

Adrien89 Double-check your formula syntax. The screenshot doesn't show the entire formula, but from what is visible, I can see that the first use of the AND function is missing the closing bracket and comma between "Gatton" and 1940. It should be =IF(AND(B58="Adelaide",D58="Gatton"),1940,…)

  • Adrien89's avatar
    Adrien89
    Copper Contributor
    Dec 07, 2023

    djclements 


    =IF(AND(B58="Adelaide",D58="Gatton"),1940,IF(AND(B58="MELBOURNE"),735,IF(AND(B58="ADELAIDE",d58="SYDNEY"),1385,IF(AND(B58="BELYANDO",D58="BRISBANE"),1150,IF(AND(b58="BELYANDO",D58="GATTON"),1070,IF(AND(B58="Bloomsbury",D58="BRISBANE"),1085,IF(AND(B58="BLOOMSBURY",D58="GATTON"),1110,IF(AND(B58="BOWEN",D58="BRISBANE"),1190,IF(AND(B58="BOWEN",D58="GATTON"),1215,IF(AND(B58="BOWEN",D58="MELBOURNE"),2515,IF(AND(B58="BRISBANE",d58="Belyando"),1150,IF(AND(B58="BRISBANE",D58="BLOOMSBURY"),1085,IF(AND(B58="BRISBANE",D58="BOWEN"),1190,IF(AND(B58="BRISBANE",D58="CANBERRA"),1190,IF(AND(B58="BRISBANE",D58="GATTON"),90,IF(AND(B58="BRISBANE",D58="MACKAY"),995,IF(AND(B58="BRISBANE",D58="MELBOURNE"),1790,IF(AND(B58="BRISBANE",D58="NAMBUCCA"),430,IF(AND(B58="BRISBANE",D58="PROSERPINE"),1120,IF(AND(B58="BRISBANE",D58="SHEPPARTON"),1470,IF(AND(B58="BRISBANE",D58="ST GEORGE"),490,IF(AND(B58="BRISBANE",D58="SYDNEY"),950 IF(AND(B58="BRISBANE",D58="TOWNSVILLE"),1400,IF(AND(B58="CANBERRA",D58="BRISBANE"),1190,IF(AND(B58="CANBERRA",D58="MELBOURNE"),680,IF(AND(B58="CANBERRA",D58="SHEPPARTON"),525,IF(AND(B58"GATTON",D58="ADELAIDE",)1940,IF(AND(B58="GATTON",D58="BELYANDO"),1070,IF(AND(B58="GATTON",D58="BLOOMSBURY"),1110,IF(AND(B58="GATTON",D58="BOWEN"),1215,IF(AND(B58="GATTON",D58="BRISBANE"),90,IF(AND(B58="GATTON",D58="GOONDIWINDI"),.265,IF(AND(B58="GATTON",D58="MACKAY"),1020,IF(AND(B58="GATTON",D58="MELBOURNE"),1685))))))))))))))))))))))))))))))))))))))))

    This is my current full formula. But it's not completed yet as there is 78 options in total that I need to put in

    • peiyezhu's avatar
      peiyezhu
      Bronze Contributor
      Dec 07, 2023
      if possible,share your Excel .xlsx file.
    • djclements's avatar
      djclements
      Bronze Contributor
      Dec 07, 2023

      Adrien89 Just to confirm, what spreadsheet program are you using? You've tagged Office 365, but the screenshot doesn't look like Excel. Is it Google Sheets? Or something else?

      • Adrien89's avatar
        Adrien89
        Copper Contributor
        Dec 07, 2023
        This screenshots is in excel. I did that on my tablet but was doing the actual spreadsheet on my phone in 365

Resources