Home

Finding Matches in List of Zip Codes

%3CLINGO-SUB%20id%3D%22lingo-sub-907191%22%20slang%3D%22en-US%22%3EFinding%20Matches%20in%20List%20of%20Zip%20Codes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-907191%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20list%20of%20zip%20codes%20in%20row%20D%20that%20attendees%20of%20a%20conference%20have%20entered.%20We%20can%20only%20provide%20lodging%20for%20those%20within%20a%2030%20mile%20radius%20of%20the%20conference.%20In%20row%20G%2C%20I%20have%20all%20zip%20codes%20that%20are%20within%20that%2030%20mile%20radius%20(there%20are%20693).%20I%20want%20to%20create%20a%20formula%20that%20I%20can%20use%20in%20conditional%20formatting%20so%20that%20the%20box%20turns%20green%20when%20the%20zip%20code%20is%20within%20a%2030%20mile%20radius.%20What%20formula%20do%20I%20need%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-907191%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-907237%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20Matches%20in%20List%20of%20Zip%20Codes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-907237%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F423998%22%20target%3D%22_blank%22%3E%40kristensemple1290%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20the%20zip%20codes%20of%20all%20the%20attendees%20are%20in%20column%20D%20starting%20from%20D2%20and%20list%20of%20all%20the%20zip%20codes%20in%2030%20mile%20radius%20are%20in%20column%20G%20starting%20from%20G2%2C%20then%20if%20you%20need%20to%20apply%20the%20conditional%20formatting%20to%20the%20range%20D2%3AD100%2C%20select%20the%20range%20D2%3AD100%20and%20make%20a%20New%20Rule%20for%20Conditional%20Formatting%20using%20the%20formula%20given%20below%20and%20set%20the%20format%20as%20per%20your%20choice.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DAND(D2%26lt%3B%26gt%3B%22%22%2CSUMPRODUCT(--(%24G%242%3A%24G%24693%3DD2))%26gt%3B0)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-908521%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20Matches%20in%20List%20of%20Zip%20Codes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-908521%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3BThat%20didn't%20seem%20to%20work%20-%20I%20can't%20figure%20out%20what's%20going%20wrong%20here.%20I've%20attached%20a%20screenshot%20of%20a%20sample%20of%20my%20list%20in%20case%20that%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-908541%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20Matches%20in%20List%20of%20Zip%20Codes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-908541%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%20I%20posed%20the%20question%20wrong%20-%20I%20can%20only%20offer%20lodging%20for%20those%20OUTSIDE%20of%20a%2030%20mile%20radius%20-%20so%20sorry!%20I'd%20like%20to%20format%20the%20cells%20in%20column%20D%20when%20they%20have%20a%20match%20to%20any%20of%20the%20cells%20in%20column%20G.%20Thank%20you%20again%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-908622%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20Matches%20in%20List%20of%20Zip%20Codes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-908622%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F423998%22%20target%3D%22_blank%22%3E%40kristensemple1290%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20did%20you%20try%20which%20didn't%20work%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20all%20you%20want%20is%20to%20highlight%20the%20Zip%20Codes%20in%20Column%20D%20if%20they%20are%20found%20in%20Column%20G%2C%20the%20formula%20I%20suggested%20will%20work.%20Please%20refer%20to%20the%20attached%20in%20which%20I%20have%20applied%20the%20conditional%20formatting%20to%20the%20range%20D1%3AD100.%20Isn't%20it%20what%20you%20are%20trying%20to%20achieve%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20please%20avoid%20posting%20image%20of%20the%20data%2C%20no%20one%20likes%20to%20retype%20the%20data%20if%20a%20file%20is%20required%20to%20show%20that%20the%20proposed%20solution%20works%20properly.%20It's%20always%20a%20good%20practice%20to%20upload%20a%20sample%20file%20instead%20of%20an%20image.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-909144%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20Matches%20in%20List%20of%20Zip%20Codes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-909144%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3BThis%20worked%2C%20thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-909213%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20Matches%20in%20List%20of%20Zip%20Codes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-909213%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F423998%22%20target%3D%22_blank%22%3E%40kristensemple1290%3C%2FA%3E!%3C%2FP%3E%3CP%3EPlease%20take%20a%20minute%20to%20accept%20the%20post%20with%20the%20proposed%20solution%20as%20a%20Best%20Response%2FAnswer%20to%20mark%20your%20question%20as%20Solved.%3C%2FP%3E%3C%2FLINGO-BODY%3E
kristensemple1290
New Contributor

I have a list of zip codes in row D that attendees of a conference have entered. We can only provide lodging for those within a 30 mile radius of the conference. In row G, I have all zip codes that are within that 30 mile radius (there are 693). I want to create a formula that I can use in conditional formatting so that the box turns green when the zip code is within a 30 mile radius. What formula do I need?

6 Replies

@kristensemple1290 

Assuming the zip codes of all the attendees are in column D starting from D2 and list of all the zip codes in 30 mile radius are in column G starting from G2, then if you need to apply the conditional formatting to the range D2:D100, select the range D2:D100 and make a New Rule for Conditional Formatting using the formula given below and set the format as per your choice.

 

=AND(D2<>"",SUMPRODUCT(--($G$2:$G$693=D2))>0)

 

@Subodh_Tiwari_sktneer That didn't seem to work - I can't figure out what's going wrong here. I've attached a screenshot of a sample of my list in case that helps.

@Subodh_Tiwari_sktneer  I posed the question wrong - I can only offer lodging for those OUTSIDE of a 30 mile radius - so sorry! I'd like to format the cells in column D when they have a match to any of the cells in column G. Thank you again for your help!

@kristensemple1290 

What did you try which didn't work?

 

If all you want is to highlight the Zip Codes in Column D if they are found in Column G, the formula I suggested will work. Please refer to the attached in which I have applied the conditional formatting to the range D1:D100. Isn't it what you are trying to achieve?

 

Also, please avoid posting image of the data, no one likes to retype the data if a file is required to show that the proposed solution works properly. It's always a good practice to upload a sample file instead of an image.

 

 

@Subodh_Tiwari_sktneer This worked, thank you!

You're welcome @kristensemple1290!

Please take a minute to accept the post with the proposed solution as a Best Response/Answer to mark your question as Solved.

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