﻿﻿﻿﻿ May 2016 – tecivo BLOG

## Introduction

Using VLOOKUP, NOT & ISNA in a Conditional Formatting rule is a really useful way to show whether a cell’s contents appear in a second data set.

To highlight Sheet1 Column A cells that appear in Sheet2 Column A, select Sheet1 Column A and create a new Conditional Formatting rule using ‘Use a formula to determine which cells to format‘ and use formula:

`=NOT(ISNA(VLOOKUP(A1,Sheet2!\$A:\$A,1,FALSE)))`

A B
1 Ford
2 Lotus
3 BMW
4 Audi
5 Mini
6 Ford

#### Sheet2

A B C D
1 Ford US Escort Red
2 Lotus UK Elise Silver
3 BMW Germany 330 Blue
4 VW Germany Golf Black
5 Ford US Mondeo Silver
6 Mini Cooper

To apply format rule to show non-matches use

`=ISNA(VLOOKUP(A1,Sheet2!\$A:\$A,1,FALSE))`
A B
1 Ford
2 Lotus
3 BMW
4 Audi
5 Mini
6 Ford

### Also see

If you want to make Excel display a + and for positive and negative number values create a Custom Format

A B C < B format rule > < C format rule >
1 12 +12 +12.00 +0;-0 +0.00;-0.00
2 -10 -10 -10.00 +0;-0 +0.00;-0.00
3 7 +7 +7.00 +0;-0 +0.00;-0.00
4 -5.4 -5.4 -5.40 +0;-0 +0.00;-0.00
5 8.691 +9 +8.67 +0;-0 +0.00;-0.00

Select the cell(s), (or entire column/row), and open Format Cells, then select Custom and in Type enter format as required as shown above.

Note: if you’re using decimal places, values will round up or down as shown in example