# COMPARE MULTIPLE CELLS

#### Joyce

##### New Member
I would like to compare: cells A2:E2 to G2:K600

If anything in A2 to E2 does not match to section G thru K, I would like to highlight it.

My sample data is:
Columns A to E:
4600 46 01 999999 Smith, Joe

Compare to
Columns G to K:
4600 10 03 666666 Day, Night
4600 46 02 999999 Smith, Joe
4410 46 01 777777 Night, Day

In this example, I'd like to highlight the 01 in the top Joe Smith line because that is the only item that is different.

Hopefully someone has an idea. Thank you for your help and your time.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi,

The long formula in A7 has to be entered with Ctrl + Shift + Enter.

It shows on which row in the list you get best match. In this example 5 matches on row 2. if there are more "5 matches" only first will be shown.

The on row 4 you will see TRUE / FALSE if that certain cell is matching or not.
Book1
ABCDEFGHIJKL
1searchList
24600461999999Smith,Joe4600103666666Day,Night
3Flag4600462999999Smith,Joe
4TRUETRUEFALSETRUETRUETRUE4410461777777Night,Day
54600101666666Day,Night
6Closestmatch4600465999999Smith,Joe
724600462999999Smith,Joe
84600463999999Smith,Joe
Sheet1

Assuming your data in column g to K is from row 1 to 3
Use format, conditional formating ,condition1 formula is
For cell a1:
=INDEX(G\$1:G\$3,MATCH(\$E1,\$K\$1:\$K\$3))<>A1
then click the format box , bold or anything you like ,click Ok,oK
After exiting
copy the format to cell b1 to d1
then in cell e1, open teh conditional formating then formula is
=COUNTIF(\$K\$1:\$K\$3,E1)=0
then choose your format like bold,ok,ok

Thank you so much! You solved it so quick when I had been spinning my wheels trying this or that.

I used Chitosunday's version & it is PERFECT! (why didn't I think of that??)

Thanks, again.

Replies
3
Views
267
Replies
16
Views
531
Replies
6
Views
157
Replies
11
Views
392
Replies
19
Views
371

1,203,400
Messages
6,055,180
Members
444,768
Latest member
EMGVT

### 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