tecivo BLOG

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

Tag: ISNA

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

7th May 2016 / / 0 Comments

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…Using VLOOKUP and & to CONCATENATE cells before returning custom text for match/no match found

19th February 2015 / / 0 Comments

When using VLOOKUP with data that isn’t made up of unique values you need to combine values to create a unique reference

You can either create additional columns in both data sources and populate with & or CONCATENATE, or you can combine the cell grouping into the VLOOKUP function.

Example – combine Sheet1 columns A and B  to create a unique reference, then use VLOOKUP to search for same in Sheet2 (where an additional column has been created and populated with & or CONCATENATE as appropriate)

=IF(ISNA(VLOOKUP(A1&B1,Sheet2!$A:$A,1,FALSE)),"not found","found")

Sheet1

A B C < C function >
1 Ford Escort found =IF(ISNA(VLOOKUP(A1&B1,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)
2 Lotus Elise found =IF(ISNA(VLOOKUP(A2&B2,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)
3 BMW 330 found =IF(ISNA(VLOOKUP(A3&B3,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)
4 Audi not found =IF(ISNA(VLOOKUP(A4&B4,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)
5 Mini Cooper found =IF(ISNA(VLOOKUP(A5&B5,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)
6 Ford Focus not found =IF(ISNA(VLOOKUP(A6&B6,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)

Sheet2

A B C D E < A function >
1 FordEscort Ford US Escort Red =B1&D1
2 LotusElise Lotus UK Elise Silver =B2&D2
3 BMW330 BMW Germany 330 Blue =B3&D3
4 VWGolf VW Germany Golf Black =B4&D4
5 FordMondeo Ford US Mondeo Silver =B5&D5
6 MiniCooper Mini Cooper =B6&D6

Also see

 

Excel Tips…Using VLOOKUP to check if cell value found in second data set

19th January 2015 / / 0 Comments

Use VLOOKUP, IF and ISNA to find whether cell value appears in comparison data set.

Example: Search for A1 value in Sheet2 Column A and if found return Found. If not found, return Not Found

=IF(ISNA(VLOOKUP(A1,Sheet2!A:A,1,FALSE)),"Not found","Found")

Sheet1

A B < B function >
1 Ford Found =IF(ISNA(VLOOKUP(A1,Sheet2!A:A,1,FALSE)),”Not found”,”Found”)
2 Lotus Found =IF(ISNA(VLOOKUP(A2,Sheet2!A:A,1,FALSE)),”Not found”,”Found”)
3 BMW Found =IF(ISNA(VLOOKUP(A3,Sheet2!A:A,1,FALSE)),”Not found”,”Found”)
4 Audi Not Found =IF(ISNA(VLOOKUP(A4,Sheet2!A:A,1,FALSE)),”Not found”,”Found”)
5 Mini Found =IF(ISNA(VLOOKUP(A5,Sheet2!A:A,1,FALSE)),”Not found”,”Found”)

Sheet2

A B C D E F G
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

Also see

 

© 2017 tecivo BLOG

Theme by Anders Noren — Up ↑