Forum Discussion
Excel 2019
My workbook has 2 tabs, Stores and Final.
This is what I'd like to do:
If Parent _ID (from column B of Stores) is 9 insert the text Southern into Column B of Final
If Parent_ID (from Stores) is 21 insert the text Northern into Column B of Final
If Parent_ID (from Stores) is 1 insert the text HO into Column B of Final
This is the formula I created:
=IF(B2=1,Stores!$a$1:$j$33,"HO",IF(B2=21,Stores!$a$1:$j$33"Northern",IF(B2=9,Stores!$a$1:$j$33"Southern")))
What am I missing?
Sorry, forgot attachement
8 Replies
marty007 Try it this way:
=LOOKUP(Stores!B2,{1,9,21},{"HO","Southern","Northern"})If you insist on using the IF function, and assuming that that the only options ar 1, 9 or 21, then you could try this:
=IF(Stores!B2=1,"HO",IF(Stores!B2=9,"Southern","Northern"))After I posted my question, I realized it is a lot more complicated than I thought. Here are the details. I have attached the spreadsheet. However; I understand if this is too much work for you and you would rather not work on this any further. I do appreciate your original response but I asked the wrong question, sorry.
I am trying to populate the Region column in the myFinl_Data tab
I need 3 regions, Northern, Southern, Home
I have a Store_Id that rolls up to a Parent_ID in the Stores tab
The Store_IDs 2,3,4,7,8,11,15,16,17,18,19,22.29,31 need to be in the Northern region which is Parent_ID 21
The Store-IDs 5,6,10,12,13,14,20,23,24,25,28,27,28,30,32 need to be in the Southern region which is Parent_ID 9
The Store-ID 1 needs to be in the Home region which is Parent_ID 0
The Store_IDs 9,21 also need to be in the Home region which is Parent_ID 1
Parent_ID 21= (Northern) If city= Baltimore, Raleigh,Washington,Wilmington enter Northern
Parent_ID 9=(Southern) if city= Philadelphia,New York,Jersey City enter Southern
Note: Jersey City appears as Parent_ID 9,0,and 1. The Regional offices (Parent_ID 1) are supposed to roll up into the Home region (Parent_ID 0)
My thought was to change the name of Jersey City with Parent_IDs 0 or 1 to JerseyCity M, to avoid conflict with the other Jersey City store
0,1=Jersey City M, region Home
If I understood the logic correctly, you may define helper range for the regions
and use
=IFERROR(INDEX($H$21:$H$23, MATCH( INDEX(Stores!$B$2:$B$33, MATCH(D2,Stores!$A$2:$A$33,0)), $G$21:$G$23, 0)), "not defined")Please check attached.
