Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

This question already has an answer here:

This question is posted as a spinoff from here - the post cited is about Mac where as this is about Windows:

Conditional Formatting: 3 color scale showing text relevance in Excel for Mac

Is there a way to use conditional formatting in the following manner in Excel? (For those who are curious, I am using Excel 2010 for Mac)

I want Cell B2 to be green when A2 says "John likes green apples" and B2 also says "John likes green apples", yellow if A2 says "John likes green apples" and B2 says "Bill likes blue apples", and red if A2 says "John likes green apples" and B2 says "Bill hates red oranges".

I am just wondering if there is a way to something to that effect.


The question noted above is asking specifically for an Excel for Mac solution, so can we achieve the same thing on PC-based Excel?

share|improve this question

marked as duplicate by Raystafarian, Excellll, Moses, Ƭᴇcʜιᴇ007, Scott May 20 '14 at 21:23

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

Voted to close because the solutions in the other thread sufficiently answer this question. –  Raystafarian May 15 '14 at 16:17
I was specifically asked by a mod to move my answer away from that question and post a separate question specific to PC users. –  Andi Mohr May 15 '14 at 16:36
Actually, the mod wrote so perhaps Andi Mohr can delete this and post a separate question / answer pair? - it was a question, not a suggestion. I re titled the question to make it clearer because Raystafarian is right, it does initially appear as a dupe... But, since it is for PC and not Mac I do think it's not a dupe –  Dave May 20 '14 at 9:33
Closing the question isn't necessarily a bad thing. The question is still accessible and is linked to the original post. Personally, I don't see the purpose of posting this as a separate question because, as @Raystafarian noted, the other question had answers that work for Mac as well. An edit to broaden the original question was probably the best course of action, but no worries now. The goal is to help, and this remains helpful even if closed. –  Excellll May 20 '14 at 14:05

1 Answer 1

up vote 2 down vote accepted

The Microsoft Fuzzy Lookup Add-in does a great job for this kind of thing. Sadly, it won't work for Mac users, but does for PC users running Excel 2010 or later.

  1. Download the add-in. (Make sure a Fuzzy Lookup tab appears on the ribbon.) enter image description here

  2. Copy all data in your first text column into a new tab/workbook. Insert a table for that column only (IE use the Insert Table tool on the ribbon). Use the Remove duplicates tool on the data tab.

  3. Do the same for your second column of data - put that into a table near your first.

  4. Activate the fuzzy lookup tool then slide the Similarity Threshold selector all the way to the left (to zero). You may wish to increase the number of matches to ensure all combinations get scored. enter image description here

  5. Select a cell to the right of your tables in row 1, then click Go in the Fuzzy Lookup pane.

  6. You should see a list of combinations and scores.

  7. Insert a column between the second column and the similarity score, and insert a concatenation of column1 & column2, like this. enter image description here

  8. In your original data, add a new column, and perform a VLOOKUP on the columns you've just created (highlighted yellow above), selecting the similarity score.

  9. Use this score to configure some conditional formatting, perhaps set a score of 1 to be green, 0 to be red and orange anything in between. enter image description here

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.