Need help with formula

[Adrien89]

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 

[#2]

@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,…)

[#3]

@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

[#4]

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

[#5]

This screenshots is in excel. I did that on my tablet but was doing the actual spreadsheet on my phone in 365

[#6]

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

[#7]

if possible,share your Excel .xlsx file.

[#8]

I'll have to have a look at this a little later tonight as I'm not the tech savvy LOL

[#9]

@Adrien89 No worries 🙂

For a detail explanation of this method, check out: https://exceljet.net/formulas/xlookup-with-multiple-criteria