Need help with formula

Copper Contributor

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 Screenshot_20231207_100751_Sheets.jpg

8 Replies

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

@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

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

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

@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)
if possible,share your Excel .xlsx file.
I'll have to have a look at this a little later tonight as I'm not the tech savvy LOL

@Adrien89 No worries :)

 

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