May 2016 – tecivo BLOG

tecivo BLOG

My IT notes, 'How To...' guides, Microsoft, SharePoint, Citrix, AppSense, Apple, technology & gadgets and more!

Month: May 2016

Excel Tips…Use Conditional Formatting with VLOOKUP to colour cell when match found

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)))

Sheet1

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

 

Excel Tips…use custom cell format so numbers display with ”+” and ”-”

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

 

© 2019 tecivo BLOG

Theme by Anders NorenUp ↑