Home

Indexing Mult. Unique Cells by Matching Cell of Table1 to Mult. Cells of Table2 - Create Dropdown

%3CLINGO-SUB%20id%3D%22lingo-sub-952515%22%20slang%3D%22en-US%22%3EIndexing%20Mult.%20Unique%20Cells%20by%20Matching%20Cell%20of%20Table1%20to%20Mult.%20Cells%20of%20Table2%20-%20Create%20Dropdown%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-952515%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20tables.%20One%20is%20a%20list%20of%20primary%20keys%20I%20want%20to%20return%20data%20for.%20The%20other%20table%20is%20a%20list%20of%20duplicate%20primary%20keys%20with%20unique%20cells.%20I%20want%20to%20match%20primary%20keys%20and%20have%20a%20drop%20down%20of%20the%20unique%20values%20associated%20with%20the%20primary%20key.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20Primary%20Key%26nbsp%3Bis%20an%26nbsp%3BUSPS%20ZIP%20Code.%20Table%20One%20has%20a%20specific%20set%20of%20ZIP%20Codes%20I%20need%20to%20return%20data%20for.%20Table%20Two%20has%20a%20list%20of%20every%20ZIP%20Code%20and%20every%20corresponding%26nbsp%3BCity.%20Every%20ZIP%20Code%20has%20multiple%20Cities.%20(i.e.%20ZIP%20Code%2063122%20includes%20the%20USPS%26nbsp%3BPrimary%20City%20Name%20Saint%20Louis%20and%20an%26nbsp%3BAcceptable%20Name%20Kirkwood).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20Table%20One%2C%20I%20want%20to%20match%26nbsp%3Bthe%20ZIP%20Code%2063122%26nbsp%3Bto%20the%20ZIP%20Code%3CU%3Es%3C%2FU%3E%2063122%20in%20Table%20Two%20and%20only%26nbsp%3Breturn%20the%20cities%20listed%26nbsp%3Bas%20Primary%20and%20Acceptable.%20I%20want%20the%20cities%20listed%20in%20a%20drop%20down%20so%20that%20I%20can%20select%20the%20city%20I%20want.%20A%20step%20further%2C%20I%20want%20to%20be%20able%20to%20change%20the%20Primary%20Keys%20(ZIP%20Codes)%20in%20Table%20One%20and%20have%26nbsp%3Bthe%20drop%20down%20automatically%20update.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20be%20able%20to%20type%20in%20a%20specific%20ZIP%20Code%2C%20see%20all%20associated%20Cities%20and%20select%20the%20appropriate%20City.%20I%20want%20to%20use%20this%20as%20a%20master%20file%20so%20that%20I%20can%20use%20it%20with%20multiple%20lists%20of%20ZIP%20Codes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20possible%3F%20Is%20there%20a%20better%20way%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-952515%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-955622%22%20slang%3D%22en-US%22%3ERe%3A%20Indexing%20Mult.%20Unique%20Cells%20by%20Matching%20Cell%20of%20Table1%20to%20Mult.%20Cells%20of%20Table2%20-%20Create%20Dropdow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-955622%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F433445%22%20target%3D%22_blank%22%3E%40Audit5%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECtrl%2BShift%2BEnter%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX(Sheet1!%24B%241%3A%24B%2420%2CSMALL(IF(Sheet1!%24A%241%3A%24A%2420%3D'2019%20ZIP%20Codes%20and%20Cities'!%24A%242%2CROW(Sheet1!%24A%241%3A%24A%2420))%2CROW(A1))%2C1)%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPull%20vertically%20as%20far%20as%20needed.%20Error%20check%20included%20so%20you%20won't%20get%20%23NUM%20errors%20when%20there's%20no%26nbsp%3B%20more%20cities%20to%20pull.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20FILTER%20function%20that's%20an%20option%2C%20too.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Audit5
Occasional Visitor

I have two tables. One is a list of primary keys I want to return data for. The other table is a list of duplicate primary keys with unique cells. I want to match primary keys and have a drop down of the unique values associated with the primary key.

 

My Primary Key is an USPS ZIP Code. Table One has a specific set of ZIP Codes I need to return data for. Table Two has a list of every ZIP Code and every corresponding City. Every ZIP Code has multiple Cities. (i.e. ZIP Code 63122 includes the USPS Primary City Name Saint Louis and an Acceptable Name Kirkwood).

 

In Table One, I want to match the ZIP Code 63122 to the ZIP Codes 63122 in Table Two and only return the cities listed as Primary and Acceptable. I want the cities listed in a drop down so that I can select the city I want. A step further, I want to be able to change the Primary Keys (ZIP Codes) in Table One and have the drop down automatically update.

 

I need to be able to type in a specific ZIP Code, see all associated Cities and select the appropriate City. I want to use this as a master file so that I can use it with multiple lists of ZIP Codes.

 

Is this possible? Is there a better way?

1 Reply

@Audit5 

 

Ctrl+Shift+Enter

=IFERROR(INDEX(Sheet1!$B$1:$B$20,SMALL(IF(Sheet1!$A$1:$A$20='2019 ZIP Codes and Cities'!$A$2,ROW(Sheet1!$A$1:$A$20)),ROW(A1)),1),"")

 

Pull vertically as far as needed. Error check included so you won't get #NUM errors when there's no  more cities to pull.

 

If you have FILTER function that's an option, too.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies