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.

 

 

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
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies