Forum Discussion
How to make an Array formula with multiple nested if statements ? is there a better way?
Hello,
I am trying to create an IF formula that automates whether a destination is domestic or international. The destination will be entered by its airport code (BGI,LAS,) and a separate cell will determin if it is International or not. I need this formula to be used in over 3k cells in the same row how would I array the formula to make it cleaner instead of coping and pasting 3k formulas.
Example:
If one of the below locations are entered in lets say cell i1 then Cell J1 I want it to show International or domestic
I1=BGI Then Cell J1 with the formula automates if it is international or domestic. In this case its International.
| Domestic | International |
| ABQ | AUA |
| ACK | BDA |
| ANU | BGI |
| ATL | CTG |
Please help the formula below I have works but I have more arguments then I posted below its about 100 plus
=IF(I4="BDA","International",IF(I4="BGI","International",IF(I4="AUA","International",IF(I4="CTG","International",IF(I4="ABQ","Domestic",IF(I4="ACK","Domestic",IF(I4="ANU","Domestic",IF(I4="ATL","Domestic"))))))))
6 Replies
John Sokolowski If you reformat your table so it has airport code in column A and DOmestic or International in column B you can use VLOOKUP to get the information you need.
Thank you , So I did do that but one of the vlookups keeps returning the wrong value and I don't know why.
John Sokolowski , if your airports are in columns A and B starting from first row where are column names, when it could be
=IF(ISNUMBER(MATCH(I1,$A:$A,0)),$A$1,IF(ISNUMBER(MATCH(I1,$B:$B,0)),$B$1,"no such"))
