Highlighting Rows when 2 columns don't match from another sheet.

[steveh8204]

Hi, firstly I'm not sure if the title explains very well my issue so I'll explain what the function needs to do.

I have two sheets with numerous columns on, some similar, some not. On my first sheet I need to look up the value in A1 for the similar value in the second sheet but I need to look up the value in Column B for the value as well and it needs to match column b on the second sheet.

The picture below probably better explains it.

https://drive.google.com/file/d/1qGJVvyL6wK0qbmlHk3Sb8t1F0_WD-Nem/view?usp=sharing

Thanks in advance for any help.

 

[oldbrewer]

[TABLE="width: 1088"]
<colgroup><col span="17"></colgroup><tbody>[TR]
[TD]PONUM[/TD]
[TD]KCODE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]pretend second sheet[/TD]
[TD]PONUM[/TD]
[TD]KCODE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PO1[/TD]
[TD]CODE1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PO9[/TD]
[TD]CODE9[/TD]
[TD][/TD]
[TD="colspan: 2"]VALUES IN K2:L10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PO2[/TD]
[TD]CODE2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PO8[/TD]
[TD]CODE88[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PO3[/TD]
[TD]CODE3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PO7[/TD]
[TD]CODE7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PO4[/TD]
[TD]CODE4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PO6[/TD]
[TD]CODE6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PO5[/TD]
[TD]CODE5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PO5[/TD]
[TD]CODE55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PO6[/TD]
[TD]CODE6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PO4[/TD]
[TD]CODE4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PO7[/TD]
[TD]CODE7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PO3[/TD]
[TD]CODE33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PO8[/TD]
[TD]CODE8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PO2[/TD]
[TD]CODE2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PO9[/TD]
[TD]CODE9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PO1[/TD]
[TD]CODE1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 9"]PO8,PO5,PO3 ARE COLOURED RED BY THIS CONDITIONAL FORMATTING FORMULA[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]IN K2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]=OFFSET($A$1,MATCH(K2,$A$2:$A$10,0),1)<>L2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[brownbread]

Imperfect answer: Add conditional formatting with this formula, to the cells in both columns on the sheet you want to highlight. This assumes your data is in columns A and B starting on row 2, and that your second sheet is called Sheet2, and that your second sheet data appears on rows 2 to 10:

=IF(MATCH($A2,Sheet2!$A$2:$A$10,0)=MATCH($B2,Sheet2!$B$2:$B$10,0),1,0)

Edit: Didn't realise answer already provided... AND my answer won't work if a there are non-unique matches in either column... to get a match in line an array formula works nicely, =VLOOKUP(A2&B2,Sheet2!$A$2:$A$10&Sheet2!$B$2:$B$10,1,0) (enter with CTRL+SHIFT+enter)

 

[oldbrewer]

BROWNBREAD = I supplied a methodology to the OP and assumed he could cond format the other column if required

 

[steveh8204]

Imperfect answer: Add conditional formatting with this formula, to the cells in both columns on the sheet you want to highlight. This assumes your data is in columns A and B starting on row 2, and that your second sheet is called Sheet2, and that your second sheet data appears on rows 2 to 10:

=IF(MATCH($A2,Sheet2!$A$2:$A$10,0)=MATCH($B2,Sheet2!$B$2:$B$10,0),1,0)

Edit: Didn't realise answer already provided... AND my answer won't work if a there are non-unique matches in either column... to get a match in line an array formula works nicely, =VLOOKUP(A2&B2,Sheet2!$A$2:$A$10&Sheet2!$B$2:$B$10,1,0) (enter with CTRL+SHIFT+enter)

I've used this one since these is the one I understand the most to adapt but I've been hit with an error message when I've created this in Conditional Formatting:

"You may not use reference operators or array constants for Conditional Formatting criteria".

I didn't understand exactly what I was supposed to put where for the other answer if I'm honest.

One of the sheet is called "SAP Korozo" and has the "PO Number" in Column A and "SAP Code" in B. The other sheet is called "Korozo" and has the "PO Number" in Column D and the "K Number" in Row A (which is the other way around from the first which probably doesn't help. The rows start at Row 3 and I was hoping to highlight the odd ones out on sheet (Korozo).

Thanks for your assistance with this matter.

 

[oldbrewer]

in sheet korozo in the first cell with a po number this cond formatting

offset(sapkorozo!$a$1, match(d2,sapkorozo!$a$2:$a$20,0),1)<>a2

 

[steveh8204]

in sheet korozo in the first cell with a po number this cond formatting

offset(sapkorozo!$a$1, match(d2,sapkorozo!$a$2:$a$20,0),1)<>a2

Thanks for the quick reply. Looks ok except my work laptop battery died half way through checking so will have to wait til Monday. I assume to do the same with the K Codes I just do similar formatting in the K Code column and change the column references?

Thanks again for your help.

 

[oldbrewer]

use the same formula - basically - you just locate the formula in the other cell you want to color....

 

[steveh8204]

use the same formula - basically - you just locate the formula in the other cell you want to color....

Nice one. Thanks again for all your help.

 

[oldbrewer]

I am 73, retired, this forum keeps my brain cells ticking over.....(just)