1

The ID column contains all unique values. Sheet 1 is the master file. Sheet 2 contains the same IDs, but not necessarily in the same sequence. I need to identify cases where the Name in Sheet 2 does not match for the same ID on Sheet 1. I want to do this with highlighting using conditional formatting.

enter image description here

The second set of tables in the image, labelled "Correct One" shows what I'm trying to achieve. The names for IDs 11 and 16 don't match, so they are highlighted.

I have tried to use the conditional formula =A1<>sheet1!A. That is checking by cell location rather than by ID, so it incorrectly highlights IDs not in the same sequence.

Is there a way to do this?

Improve this question
5
  • I'm working with LO Calc and conditional formatting works a little different. But the gist is that you need to do a lookup to find the value to compare. For B2 on sheet2, the conditional test would be something like: value not equal to VLOOKUP(A2,Sheet1!A2:B6,2,0). For each cell in the Name column, you are finding its ID in sheet 1 and comparing to the Name value for that ID in sheet 1.
    – fixer1234
    Commented Apr 14, 2017 at 19:14
  • 1
    Comments are not for extended discussion; this conversation has been moved to chat.
    – Mokubai ♦
    Commented Apr 14, 2017 at 19:32
  • How to put symbol value not equal to, at start of vlookup in conditianal test .
    – safwan
    Commented Apr 14, 2017 at 20:19
  • The "not equal to" should be a selection in the conditional formatting wizard, similar to selecting the cell value as what you want to compare.
    – fixer1234
    Commented Apr 14, 2017 at 20:42
  • You could also select the formula option, where the formula must evaluate to true. So it would look like: =B2<>VLOOKUP(A2,Sheet1!$A$2:$B$6,2,0)
    – fixer1234
    Commented Apr 14, 2017 at 21:06

1 Answer 1

Reset to default
1

On 'sheet 2'.B3 cell apply conditional formatting where value is not equal result of vertical search function like this:

Libre office syntax 

VLOOKUP('sheet 2'.$A3;'sheet 1'.$A$3:$B$7;2;0)

MS Excel syntax 

VLOOKUP('sheet 2'!$A3,'sheet 1'!$A$3:$B$7,2,0)

with arguments

  • SearchCriterion = 'sheet 2'.$A3 - reference to ID value from sheet 2 searched in sheet 1 (absolute column and relative row)
  • Array = 'sheet 1'.$A$3:$B$7 - absolute reference to array in sheet 1
  • Index = 2 - number of the column in the array that contains the value to be returned
  • Sorted = 0 - unsorted

next, use Format Painter to copy conditional formatting to other cell in column.

Improve this answer
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .