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 then cell f58 will show the correct number.

I was advised to use the If(AND( but it doesn't want to work for me. Any help is appreciated 

  • djclements's avatar
    djclements
    Bronze Contributor

    Adrien89 A simpler approach would be to list all of the possible departure and destination locations in a lookup table, like you have started in cell B74, then use the XLOOKUP function, if it's available in your program. For example:

     

    =XLOOKUP(1, (B58=$B$75:$B$1000)*(D58=$C$75:$C$1000), $D$75:$D$1000, 0)
  • djclements's avatar
    djclements
    Bronze Contributor

    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

      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
        if possible,share your Excel .xlsx file.

Resources