• 461K Members
• 5,419 Online
• 559K Conversations

New Contributor

# Finding Matches in List of Zip Codes

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

# Re: Finding Matches in List of Zip Codes

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)

# Re: Finding Matches in List of Zip Codes

@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.

# Re: Finding Matches in List of Zip Codes

@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!

# Re: Finding Matches in List of Zip Codes

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.

# Re: Finding Matches in List of Zip Codes

@Subodh_Tiwari_sktneer This worked, thank you!

# Re: Finding Matches in List of Zip Codes

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