Format values based on whether they appear in another column

• Introduction
• Method

How to use conditional formatting to highlight cells that appear in another list.

Introduction

You have a whole range of data (multiple columns and rows), and you wish to highlight cells based on whether they appear in another list (such as absent students)? No problems, custom conditional formatting is here to help!

Method

Let us assume that the list upon which you want to match is in column A of your sheet, and the data you want to highlight (where a duplicate is found), is in columns B-Z.

Select this range (columns B onwards), and apply a conditional format (Format -> Conditional formatting…). Select Custom Format from the ‘Format cells if…’ dropdown, and then enter the following formula (including the initial = symbol).

IF(MATCH(B1,$A$1:$A,0)>=0,TRUE,FALSE)

Then select the format you wish to apply to the cells, and those cells that match a value in your A column will be highlighted.

Easy!