Forum Discussion
Excel 2019
Sorry, forgot attachement
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.
What you did works. Way beyond my skill level. I'm trying to understand what you did. See my comments in the attached file. Thanks for the grea job you did.
