SOLVED

Excel 2019

Brass Contributor

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?

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

 

@Riny_van_Eekelen 

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   

@marty007 

If I understood the logic correctly, you may define helper range for the regions

image.png

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.

@Sergei Baklan 

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.

@marty007 

Perhaps that will be bit easier if we convert data in structured tables. Let name them

Stores

image.png

 

Data and Regions

image.png

Convert formula to use structured references and apply some formatting

=IFERROR(
    INDEX(Regions[Region],
          MATCH( INDEX(Stores[Parent_ID],
                 MATCH( [@[Store_ID]],
                         Stores[Store_ID],0)
                 ),
                 Regions[Parent ID], 0)
    ),
 "not defined")

Internal MATCH returns position of current Store_ID in table Stores.

Taking that position INDEX returns value of Parent_ID in table Stores

Next MATCH finds position of returned Parent_ID in the table Regions

Upper INDEX returns Region value in Regions table for the record number returned by previous MATCH

Finally we wrap by IFERROR which returns some text if nothing was found, i.e. internal formulae returned an error.

 

Please check in another two sheets in attached file.

best response confirmed by marty007 (Brass Contributor)
Solution

@marty007 

Sorry, forgot attachement

Thank you very much. this helps me try to make sense of the logic. I appreciate all your help.

@marty007 , you are welcome

1 best response

Accepted Solutions
best response confirmed by marty007 (Brass Contributor)
Solution

@marty007 

Sorry, forgot attachement

View solution in original post