Oct 10 2019 01:33 PM
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?
Oct 10 2019 01:53 PM
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)
Oct 11 2019 06:01 AM
@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.
Oct 11 2019 06:11 AM
@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!
Oct 11 2019 06:59 AM
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.
Oct 11 2019 09:01 AM
@Subodh_Tiwari_sktneer This worked, thank you!
Oct 11 2019 09:19 AM
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.