Forum Discussion
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
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 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!
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 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.
