Partial match with conditional formatting.

Question

Hello,

i need help with matching and highlighting the result.

I have two sets of table. I need to match words in column A with the words in column D.

https://learn-attachment.microsoft.com/api/attachments/079751c9-9d15-40e8-863b-287ba3182ce4?platform=QnA

This is how i want to achieve. I need the second table to show me which of its data were matched/selected by column A.

Since column D has some more text other than what I am looking for, I guess I need to do partial match.

I am in no way an expert in excel, so I need step by step guide.

Please help!!

I will greatly appreciate any help!

Answer 1

Hi,

Assuming that the 2 leftmost letters in D are the ones that you want to match in column A.

1. Select the range D1:E6
2. In the Home tab, conditional formatting
3. New rule
4. Use a formula to determine which cells to format
5. Enter this formula: =MATCH(LEFT($D1,2),$A$1:$A$100,0)
6. Set the desired format and OK, OK

Answer 2

Try the following formula in conditional formatting      =MATCH(LEFT($D1,2),$A$1:$A$10,0)>0

The steps are as given below:

1. Select the range D1:E1
2. Click HOME >> CONDITIONAL FORMATTING
3. Under the Select a Rule Type >> 

    after selecting "Use a formula to determine which cells to format"

    Type the above mentioned formula and choose color of your choice to FILL

4. Click OK and come out
5. Using Format Painter, apply the conditional formatting to balance cells in column D & E

NOTE: In my sample formula, I have chosen the range of Column-A only from row-1 to row-10

           You may change this based on your need.

Hope this Helps.