Match data in two different columns on 1 work sheet to 2 different columns on master work sheet if don't match highlight red

[bgrove]

Hi,

I'm in need of extra brain power with this challenge.

I want to use conditional formatting to compare the name and the class to the master table
and highlight if the class does not match for that name.

I have used the following formulas
=AND(COUNTIF('MASTER'!$A$2:$A$264,A2)=1,Not(ISERROR(MATCH(B2,'MASTER'!$B$2:$B$264,0))))
**the above formula highlights all the classes red
=AND((A2='Master'!A2),(B2='Master'!B2))
**the above formula does NOT highlight the class in B2 but all the rest are highlight red

Name Class
Brad A
Carl D
Doug C
Eddie F
Frank A

Master
Name Class
Brad A
Carl E
Doug B
Eddie F
Frank A

Your suggestions and help would be greatly appreciated.

Thank you in advance,

Bev

 

[oldbrewer]

Name Class
Brad	A		######
Carl	D	ERROR
Doug	C	ERROR
Eddie	F
Frank	A
Master
Name Class			####
Brad	A		=IF(OFFSET($A$9,MATCH(A2,$A$10:$A$14,0),1)<>B2,"ERROR","")
Carl	E
Doug	B
Eddie	F		THIS IS THE LOGIC
Frank	A
			JUST PUT IT IN THE CONDITIONAL FORMATTING FORMULA
			something like
			=OFFSET($A$9,MATCH(A2,$A$10:$A$14,0),1)<>B2

<colgroup><col><col span="10"></colgroup><tbody>
</tbody>

 

[bgrove]

Name Class
Brad	A		######
Carl	D	ERROR
Doug	C	ERROR
Eddie	F
Frank	A
Master
Name Class			####
Brad	A		=IF(OFFSET($A$9,MATCH(A2,$A$10:$A$14,0),1)<>B2,"ERROR","")
Carl	E
Doug	B
Eddie	F		THIS IS THE LOGIC
Frank	A
			JUST PUT IT IN THE CONDITIONAL FORMATTING FORMULA
			something like
			=OFFSET($A$9,MATCH(A2,$A$10:$A$14,0),1)<>B2

<tbody>
</tbody>

Hi, Thanks for the quick response.

I'm getting an N/A error. I think it is because my master is on a different sheet and "Brad" shows up in the same cell A2 on the Master and on the data that I'm trying to validate. I will keep playing with it to see if I can get it to work.

 

[bgrove]

Hi,
I created a vlookup formula that words great when used in the cell to show which cell is not matching to the master. But I cannot get the formula to work in the formula field for unconditional formatting. Any suggestions?

=IF(VLOOKUP(A2,'Master'!$B$2:$G$264,1,0)=A2,IF(VLOOKUP(A2,'Master'!$B$2:$G$264,6,FALSE)=D2,"Fine","Error"))

 

[oldbrewer]

=and(VLOOKUP(A2,'Master'!$B$2:$G$264,1,0)=A2,VLOOKUP(A2,'Master'!$B$2:$G$264,6,FALSE)=D2)

the last = may have to be <>

 

[bgrove]

=and(VLOOKUP(A2,'Master'!$B$2:$G$264,1,0)=A2,VLOOKUP(A2,'Master'!$B$2:$G$264,6,FALSE)=D2)

the last = may have to be <>

HUGE THANK YOU! I made the adjustments to my formula and it worked. I dub thee " King of Excel"

Bev

 

[oldbrewer]

Bev - you are very welcome - helping people on here keeps my 71 year old brain functioning..............