Home

How to make an Array formula with multiple nested if statements ? is there a better way?

%3CLINGO-SUB%20id%3D%22lingo-sub-548931%22%20slang%3D%22en-US%22%3EHow%20to%20make%20an%20Array%20formula%20with%20multiple%20nested%20if%20statements%20%3F%20is%20there%20a%20better%20way%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-548931%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20an%20IF%20formula%20that%20automates%20whether%20a%20destination%20is%20domestic%20or%20international.%20The%20destination%20will%20be%20entered%20by%20its%20airport%20code%20(BGI%2CLAS%2C)%20and%20a%20separate%20cell%20will%20determin%20if%20it%20is%20International%20or%26nbsp%3Bnot.%26nbsp%3B%26nbsp%3BI%20need%20this%20formula%20to%20be%20used%20in%20over%203k%20cells%20in%20the%20same%20row%20how%20would%20I%20array%20the%20formula%20to%20make%20it%20cleaner%20instead%20of%20coping%20and%20pasting%203k%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3EIf%20one%20of%20the%20below%20locations%20are%20entered%20in%26nbsp%3Blets%20say%20cell%20i1%26nbsp%3Bthen%20Cell%20J1%20I%20want%20it%20to%20show%20International%20or%20domestic%3C%2FP%3E%3CP%3EI1%3DBGI%20Then%20Cell%20J1%20with%20the%20formula%20automates%20if%20it%20is%20international%20or%20domestic.%20In%20this%20case%20its%20International.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EDomestic%3C%2FTD%3E%3CTD%3EInternational%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EABQ%3C%2FTD%3E%3CTD%3EAUA%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EACK%3C%2FTD%3E%3CTD%3EBDA%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EANU%3C%2FTD%3E%3CTD%3EBGI%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EATL%3C%2FTD%3E%3CTD%3ECTG%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%20the%20formula%20below%20I%20have%20works%20but%20I%20have%20more%20arguments%20then%20I%20posted%20below%20its%20about%20100%20plus%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(I4%3D%22BDA%22%2C%22International%22%2CIF(I4%3D%22BGI%22%2C%22International%22%2CIF(I4%3D%22AUA%22%2C%22International%22%2CIF(I4%3D%22CTG%22%2C%22International%22%2CIF(I4%3D%22ABQ%22%2C%22Domestic%22%2CIF(I4%3D%22ACK%22%2C%22Domestic%22%2CIF(I4%3D%22ANU%22%2C%22Domestic%22%2CIF(I4%3D%22ATL%22%2C%22Domestic%22))))))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-548931%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-548988%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20an%20Array%20formula%20with%20multiple%20nested%20if%20statements%20%3F%20is%20there%20a%20better%20way%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-548988%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F114364%22%20target%3D%22_blank%22%3E%40John%20Sokolowski%3C%2FA%3E%20If%20you%20reformat%20your%20table%20so%20it%20has%20airport%20code%20in%20column%20A%20and%20DOmestic%20or%20International%20in%20column%20B%20you%20can%20use%20VLOOKUP%20to%20get%20the%20information%20you%20need.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-549299%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20an%20Array%20formula%20with%20multiple%20nested%20if%20statements%20%3F%20is%20there%20a%20better%20way%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-549299%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20%2C%20So%20I%20did%20do%20that%20but%20one%20of%20the%20vlookups%20keeps%20returning%20the%20wrong%20value%20and%20I%20don't%20know%20why.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-549321%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20an%20Array%20formula%20with%20multiple%20nested%20if%20statements%20%3F%20is%20there%20a%20better%20way%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-549321%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F114364%22%20target%3D%22_blank%22%3E%40John%20Sokolowski%3C%2FA%3E%26nbsp%3B%2C%20if%20your%20airports%20are%20in%20columns%20A%20and%20B%20starting%20from%20first%20row%20where%20are%20column%20names%2C%20when%20it%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIF(ISNUMBER(MATCH(I1%2C%24A%3A%24A%2C0))%2C%24A%241%2CIF(ISNUMBER(MATCH(I1%2C%24B%3A%24B%2C0))%2C%24B%241%2C%22no%20such%22))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-549616%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20an%20Array%20formula%20with%20multiple%20nested%20if%20statements%20%3F%20is%20there%20a%20better%20way%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-549616%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20my%20code%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20on%20the%20master%20sheet%20with%202%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20run%20the%20below%20formula%20for%20%22PSE%22%20which%20is%20supposed%20to%20%3D%20DOM%20it%20returns%20%22INTL%22.%20the%20formula%20is%20taking%20the%20information%20from%20the%20cell%20above%20PSE%20on%20the%20master%20list%20and%20returning%20that%20value%20instead%20of%20the%20value%20for%20PSE%20it%20is%20giving%20me%20the%20value%20for%20POS%20.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20table%20is%20sorted%20in%20alphabetical%20order%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(C2825%2C'Master%20Sheet'!%24A%242%3A%24C%2475%2C2%2CTRUE)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-549692%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20an%20Array%20formula%20with%20multiple%20nested%20if%20statements%20%3F%20is%20there%20a%20better%20way%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-549692%22%20slang%3D%22en-US%22%3E%3CP%3Ethis%20is%20what%20I%20am%20getting%26nbsp%3B%20I%20put%20the%20code%20in%20one%20of%20the%20cells.%20I%20used%20Hlookup%20to%20see%20if%20it%20comes%20out%20differently%20but%20it%20doesn't%26nbsp%3B.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23ffffff%22%20face%3D%22Arial%22%20size%3D%222%22%3EOrg%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23ffffff%22%20face%3D%22Arial%22%20size%3D%222%22%3ERegion%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3ESEA%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3EDOM%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3ESTI%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3EINTL%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3ESDQ%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3EINTL%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3EKIN%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3EINTL%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3EBTV%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3EDOM%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3ELAS%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3EDOM%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3EPSE%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3E%23N%2FA(%3DHLOOKUP(C2827%2CSheet3!%24A%242%3A%24BV%243%2C2%2CFALSE)%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3ESFO%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3EDOM%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3EBUR%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3E%23N%2FA%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3ESTI%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3EINTL%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3ESDQ%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3EINTL%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3EFLL%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3EDOM%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3EBQN%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3EDOM%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3EONT%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23002060%22%20face%3D%22Arial%22%20size%3D%222%22%3EDOM%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-550075%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20an%20Array%20formula%20with%20multiple%20nested%20if%20statements%20%3F%20is%20there%20a%20better%20way%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-550075%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F114364%22%20target%3D%22_blank%22%3E%40John%20Sokolowski%3C%2FA%3E%26nbsp%3B%2C%20if%20VLOOKUP%20returns%20the%20value%20above%20it%20means%20it%20didn't%20find%20PSE.%20Check%20manually%20if%20the%20match%20like%3C%2FP%3E%0A%3CPRE%3E%3DC2825%3D'Master%20Sheet'!%24A%2466%3C%2FPRE%3E%0A%3CP%3E(or%20where%20is%20that%20value%20in%20master%20sheet)%20if%20it%20returns%20TRUE%20or%20FALSE.%20That%20could%20be%20extra%20space%20like%20%22PSE%20%22%2C%20or%20non-printable%20character%2C%20or%20like.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
John Sokolowski
Occasional Contributor

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.

 

DomesticInternational
ABQAUA
ACKBDA
ANUBGI
ATLCTG

 

 

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

This is my code

 

I have a table on the master sheet with 2 columns.

 

When I run the below formula for "PSE" which is supposed to = DOM it returns "INTL". the formula is taking the information from the cell above PSE on the master list and returning that value instead of the value for PSE it is giving me the value for POS .

 

the table is sorted in alphabetical order

 

=VLOOKUP(C2825,'Master Sheet'!$A$2:$C$75,2,TRUE)

 

 

this is what I am getting  I put the code in one of the cells. I used Hlookup to see if it comes out differently but it doesn't .

 

OrgRegion
SEADOM
STIINTL
SDQINTL
KININTL
BTVDOM
LASDOM
PSE#N/A(=HLOOKUP(C2827,Sheet3!$A$2:$BV$3,2,FALSE)
SFODOM
BUR#N/A
STIINTL
SDQINTL
FLLDOM
BQNDOM
ONTDOM

 

 

 

@John Sokolowski , if VLOOKUP returns the value above it means it didn't find PSE. Check manually if the match like

=C2825='Master Sheet'!$A$66

(or where is that value in master sheet) if it returns TRUE or FALSE. That could be extra space like "PSE ", or non-printable character, or like.

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies