Jul 06 2021 01:45 PM
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?
Jul 06 2021 11:12 PM
@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"))
Jul 07 2021 10:15 AM
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
Jul 07 2021 10:34 AM
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.
Jul 07 2021 02:42 PM
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.
Jul 09 2021 01:47 AM
Perhaps that will be bit easier if we convert data in structured tables. Let name them
Stores
Data and Regions
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.
Jul 09 2021 01:48 AM
SolutionSorry, forgot attachement
Jul 09 2021 08:48 AM
Jul 09 2021 01:48 AM
Solution