﻿ VLOOKUP – tecivo BLOG

## tecivo BLOG

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

#### Tag: VLOOKUP (page 1 of 3)

If you do a VLOOKUP to a reference cell which is empty, the VLOOKUP will return How To Get Viagra Prescription in Garland Texas 0

To stop this you can apply a custom number format to the cell(s):

Note: Whatever you put after the  click @ will be appended to the cell content

## Introduction

Using source site VLOOKUP, opzioni binarie teamviewer NOT & click here ISNA in a http://www.visionarywebsite.com/?kiolsa=trend-gt-per-opzioni-binarie&737=4a Conditional Formatting rule is a really useful way to show whether a cell’s contents appear in a second data set.

To highlight go here 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

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 >

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