# Conditional Format with vlookup

#### lecxE

##### Board Regular
I have two sets of data copied into a single worksheet which I'll call dataset1 and dataset2. I made a simple version below. In this example, dataset1 is A1:B4 and dataset2 is C1:D4. In column F, I used =VLOOKUP(D1,A\$1:A\$4,1,0) to identify which values in column D exist in column A.

A | B | C | D | E | F
AA |1 | |ZZ |2 | #N/A
BB |2 | |AA |1 |AA
CC |3 | |DD |2 |DD
DD |4 | |YY |4 | #N/A

Here's where I need help. First, instead of using column F, I'd prefer to use conditional formatting to identify which values in column D exist in column A, with a yellow fill.

Second, I like to used conditional formatting (an orange fill) to show which items are identical in both datasets. In the examle, only "AA | 1" meets this criteria.

Then end result would show D3 with a yellow fill since DD is in both datasets, and D3 would be orange because A1:B1 matched D2:E2.

Any help would be appreciated.

Thanks.

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
use as cf in d1 formula is =countif(\$a\$1:\$a\$4,d1) copy format down

Thanks, that did the trick for my first issue.

ok for identical applied to a1:b4
=ISNUMBER(MATCH(\$A1&\$B1,INDEX(\$D\$1:\$D\$4&\$E\$1:\$E\$4,0),0))
and applied to d1:e4
=ISNUMBER(MATCH(\$D1&\$E1,INDEX(\$A\$1:\$A\$4&\$B\$1:\$B\$4,0),0))

Awesome, it worked perfectly! I understand the conditional formatting needs a true/false and that's what the formula does. However, I need to chew on this until I understand how it works. Thanks for the help!

Replies
3
Views
399
Replies
5
Views
756
Replies
3
Views
1K
Replies
8
Views
406
Replies
4
Views
526

1,212,144
Messages
6,106,218
Members
448,006
Latest member
oreo2996

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back