Finding Matches in List of Zip Codes

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